topic Can't Read missing data from excel in SAS Procedures

Can't Read missing data from excel

deleted_user — Mon, 23 Mar 2009 21:27:09 GMT

I'm using the following code:
PROC IMPORT OUT= WORK.SET1
DATAFILE= "C:\Documents and Settings\wkuuser\Desktop\TEST_EXCEL1.xls"
DBMS=EXCEL REPLACE;
SHEET="Sheet1";
GETNAMES=YES;
MIXED=no;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

and when I read the following data from exel:

y V1 V2 V3 V4 V5
. . . . 2 3
50 . 2 3 5 .
75 . . 4 1 0
2 3 4 5 6 7
. . . . . 1

The column for V1and v2 becomes all missing and character.
If I change the option above to MIXED = yes then everything reads in fine, but everything is character. Has anyone had a problem like this? I don't know much about the import procedure so maybe there is something I don't know about.

If I use my code above and the missing values are just blank cells in excel, then all is well, but I'm not sure I really want to have my data entered that way, and I often get data with '.' for missing.

I've never really had trouble with importing data before, so any advice would be appreciated. -Thanks

Re: Can't Read missing data from excel

deleted_user — Tue, 24 Mar 2009 18:07:47 GMT

Answering my own post:

"SAS uses the first eight rows of data to determine whether the variable should be read as character or numeric. The default setting mixed=no assumes that each variable is either all character or all numeric. If you have a variable with both character and numeric values or a variable with missing values use mixed=yes statement to be sure SAS will read it correctly. "

From: http://www.ats.ucla.edu/stat/sas/faq/rwxls8.htm

So, all I have to do is enter the first 8 rows with an impossible value like 999 and then the rest of the data set can have '.' indicators for missing values and SAS knows that they are supposed to be defined as numeric. Before Analysis I can either subset the data WHERE Y NE 999 or just use the where statement in the proc I'm using.

If anyone knows a more efficient way to do this, I'd love to hear, but this seems like it will work for me for now!

Thanks.