Importing Excel into SAS and the MIXED= option
in [SAS]
|
From: Joe Matise on 27 Jan 2010 15:11 MIXED just says if in the first X lines you see both, choose text. There is a registry setting you can change in your windows install that will tell it to scan down further. Which setting depends on which version of SAS you're running. You could also force the specific column to be CHAR (again depending on which version of sas you're running, there are slightly different answers here). -Joe On Wed, Jan 27, 2010 at 1:58 PM, Fowler, Ann < Ann.Fowler(a)seattlechildrens.org> wrote: > Hi All, > > > > I have a very simple proc import program that reads in an Excel > spreadsheet. The spreadsheet has 5 columns, 2 of which are lab values. > Some of the lab values have a '>' symbol. However, when I use the > MIXED=Yes option, the lab values were not saved as character variables > in the SAS dataset and values with the '>' symbol were set to missing. > The first record displaying the symbol is in line 15 and I assume that > these 2 variables were created as numeric variables because SAS only > scans the first 8 (?) lines before determining the format of the > variable. Shouldn't the MIXED= option take care of this? > > > > Does anyone have any solution to this problem without 'touching' the > Excel spreadsheet? I need to read in the spreadsheet as is. > > > > Any help will be much appreciated. > > > > Thanks. > > > > Ann > > > > Ann Fowler > > Clinical Data Coordinator III > > Cystic Fibrosis Therapeutics Development Network > > Coordinating Center > > Seattle Children's Research Institute > > 1100 Olive Way, Suite 500 > > Seattle, WA 98101 > > Ph: 206-884-7526 Fax: 206-987-7504 > > email:ann.fowler(a)seattlechildrens.org<email%3Aann.fowler(a)seattlechildrens.org> > > > > > > CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is > for the sole use of the intended recipient(s) and may contain confidential > and privileged information protected by law. Any unauthorized review, use, > disclosure or distribution is prohibited. If you are not the intended > recipient, please contact the sender by reply e-mail and destroy all copies > of the original message. >
From: Yu Zhang on 27 Jan 2010 16:01 hi, I am not sure if it will work for your case. but what i did before was insert a fake record in the first row, and set a character value for that column. later, in your datastep, you just say if _n_=1 then delete; I think that should get the column read as character type. HTH Yu On Wed, Jan 27, 2010 at 1:58 PM, Fowler, Ann < Ann.Fowler(a)seattlechildrens.org> wrote: > Hi All, > > > > I have a very simple proc import program that reads in an Excel > spreadsheet. The spreadsheet has 5 columns, 2 of which are lab values. > Some of the lab values have a '>' symbol. However, when I use the > MIXED=Yes option, the lab values were not saved as character variables > in the SAS dataset and values with the '>' symbol were set to missing. > The first record displaying the symbol is in line 15 and I assume that > these 2 variables were created as numeric variables because SAS only > scans the first 8 (?) lines before determining the format of the > variable. Shouldn't the MIXED= option take care of this? > > > > Does anyone have any solution to this problem without 'touching' the > Excel spreadsheet? I need to read in the spreadsheet as is. > > > > Any help will be much appreciated. > > > > Thanks. > > > > Ann > > > > Ann Fowler > > Clinical Data Coordinator III > > Cystic Fibrosis Therapeutics Development Network > > Coordinating Center > > Seattle Children's Research Institute > > 1100 Olive Way, Suite 500 > > Seattle, WA 98101 > > Ph: 206-884-7526 Fax: 206-987-7504 > > email:ann.fowler(a)seattlechildrens.org<email%3Aann.fowler(a)seattlechildrens.org> > > > > > > CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is > for the sole use of the intended recipient(s) and may contain confidential > and privileged information protected by law. Any unauthorized review, use, > disclosure or distribution is prohibited. If you are not the intended > recipient, please contact the sender by reply e-mail and destroy all copies > of the original message. >
From: Arthur Tabachneck on 27 Jan 2010 16:46 Ann, If you choose to take Joe's suggestion and change the guessingrows registry setting, take a look at data_null_'s macro for accomplishing that: http://xrl.us/bfa8qi HTH, Art -------- On Wed, 27 Jan 2010 11:58:24 -0800, Fowler, Ann <Ann.Fowler(a)SEATTLECHILDRENS.ORG> wrote: >Hi All, > > > >I have a very simple proc import program that reads in an Excel >spreadsheet. The spreadsheet has 5 columns, 2 of which are lab values. >Some of the lab values have a '>' symbol. However, when I use the >MIXED=Yes option, the lab values were not saved as character variables >in the SAS dataset and values with the '>' symbol were set to missing. >The first record displaying the symbol is in line 15 and I assume that >these 2 variables were created as numeric variables because SAS only >scans the first 8 (?) lines before determining the format of the >variable. Shouldn't the MIXED= option take care of this? > > > >Does anyone have any solution to this problem without 'touching' the >Excel spreadsheet? I need to read in the spreadsheet as is. > > > >Any help will be much appreciated. > > > >Thanks. > > > >Ann > > > >Ann Fowler > >Clinical Data Coordinator III > >Cystic Fibrosis Therapeutics Development Network > >Coordinating Center > >Seattle Children's Research Institute > >1100 Olive Way, Suite 500 > >Seattle, WA 98101 > >Ph: 206-884-7526 Fax: 206-987-7504 > >email:ann.fowler(a)seattlechildrens.org > > > > > >CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information protected by law. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e- mail and destroy all copies of the original message.
From: Joe Matise on 27 Jan 2010 17:15 Look for the Access Connectivity Engine - if you've never had Office pre-2007 on your machine, that may be the only thing you have, and may be the right one to change. It's under Office -> 12.0 -> Access Connectivity Engine -> Engines -> Excel. -Joe On Wed, Jan 27, 2010 at 4:11 PM, Fowler, Ann < Ann.Fowler(a)seattlechildrens.org> wrote: > Thanks to all for your responses. For some reason, I do not have the > Jet 4.0 Service Pack on my machine. I have asked my IT staff to look > into this. I will hopefully be able to change the registry once I have > it on my machine. > > Ann > > -----Original Message----- > From: Arthur Tabachneck [mailto:art297(a)NETSCAPE.NET] > Sent: Wednesday, January 27, 2010 1:47 PM > To: SAS-L(a)LISTSERV.UGA.EDU; Fowler, Ann > Subject: Re: Importing Excel into SAS and the MIXED= option > > Ann, > > If you choose to take Joe's suggestion and change the guessingrows > registry setting, take a look at data_null_'s macro for accomplishing > that: > > http://xrl.us/bfa8qi > > HTH, > Art > -------- > On Wed, 27 Jan 2010 11:58:24 -0800, Fowler, Ann > <Ann.Fowler(a)SEATTLECHILDRENS.ORG> wrote: > > >Hi All, > > > > > > > >I have a very simple proc import program that reads in an Excel > >spreadsheet. The spreadsheet has 5 columns, 2 of which are lab values. > >Some of the lab values have a '>' symbol. However, when I use the > >MIXED=Yes option, the lab values were not saved as character variables > >in the SAS dataset and values with the '>' symbol were set to missing. > >The first record displaying the symbol is in line 15 and I assume that > >these 2 variables were created as numeric variables because SAS only > >scans the first 8 (?) lines before determining the format of the > >variable. Shouldn't the MIXED= option take care of this? > > > > > > > >Does anyone have any solution to this problem without 'touching' the > >Excel spreadsheet? I need to read in the spreadsheet as is. > > > > > > > >Any help will be much appreciated. > > > > > > > >Thanks. > > > > > > > >Ann > > > > > > > >Ann Fowler > > > >Clinical Data Coordinator III > > > >Cystic Fibrosis Therapeutics Development Network > > > >Coordinating Center > > > >Seattle Children's Research Institute > > > >1100 Olive Way, Suite 500 > > > >Seattle, WA 98101 > > > >Ph: 206-884-7526 Fax: 206-987-7504 > > > >email:ann.fowler(a)seattlechildrens.org<email%3Aann.fowler(a)seattlechildrens.org> > > > > > > > > > > > >CONFIDENTIALITY NOTICE: This e-mail message, including any > attachments, > is for the sole use of the intended recipient(s) and may contain > confidential and privileged information protected by law. Any > unauthorized review, use, disclosure or distribution is prohibited. If > you are not the intended recipient, please contact the sender by reply > e- > mail and destroy all copies of the original message. > > > CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is > for the sole use of the intended recipient(s) and may contain confidential > and privileged information protected by law. Any unauthorized review, use, > disclosure or distribution is prohibited. If you are not the intended > recipient, please contact the sender by reply e-mail and destroy all copies > of the original message. >
From: "Fowler, Ann" on 27 Jan 2010 17:11
Thanks to all for your responses. For some reason, I do not have the Jet 4.0 Service Pack on my machine. I have asked my IT staff to look into this. I will hopefully be able to change the registry once I have it on my machine. Ann -----Original Message----- From: Arthur Tabachneck [mailto:art297(a)NETSCAPE.NET] Sent: Wednesday, January 27, 2010 1:47 PM To: SAS-L(a)LISTSERV.UGA.EDU; Fowler, Ann Subject: Re: Importing Excel into SAS and the MIXED= option Ann, If you choose to take Joe's suggestion and change the guessingrows registry setting, take a look at data_null_'s macro for accomplishing that: http://xrl.us/bfa8qi HTH, Art -------- On Wed, 27 Jan 2010 11:58:24 -0800, Fowler, Ann <Ann.Fowler(a)SEATTLECHILDRENS.ORG> wrote: >Hi All, > > > >I have a very simple proc import program that reads in an Excel >spreadsheet. The spreadsheet has 5 columns, 2 of which are lab values. >Some of the lab values have a '>' symbol. However, when I use the >MIXED=Yes option, the lab values were not saved as character variables >in the SAS dataset and values with the '>' symbol were set to missing. >The first record displaying the symbol is in line 15 and I assume that >these 2 variables were created as numeric variables because SAS only >scans the first 8 (?) lines before determining the format of the >variable. Shouldn't the MIXED= option take care of this? > > > >Does anyone have any solution to this problem without 'touching' the >Excel spreadsheet? I need to read in the spreadsheet as is. > > > >Any help will be much appreciated. > > > >Thanks. > > > >Ann > > > >Ann Fowler > >Clinical Data Coordinator III > >Cystic Fibrosis Therapeutics Development Network > >Coordinating Center > >Seattle Children's Research Institute > >1100 Olive Way, Suite 500 > >Seattle, WA 98101 > >Ph: 206-884-7526 Fax: 206-987-7504 > >email:ann.fowler(a)seattlechildrens.org > > > > > >CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information protected by law. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e- mail and destroy all copies of the original message. CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information protected by law. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. |