proc import .xlsx

[Hawkeye]

Good day all,

I am trying to import a .xlsx file and have the first line take in only what is literally in the file as far as the variables are concerned. Instead the way I have it set up now, it takes the format that is in excel and puts it in the sas datetime. format.

To give you an idea of the dataset:

building     10-jun     10-jul     10- aug ....

Is there an option in the proc import statement to ignore the format that is applied to the variables?

Thanks.

[Reeza]

You can set scantime/scandate to no in your proc import, but that might not help with other data types.

Otherwise you can see some of the options in this thread here:

http://listserv.uga.edu/cgi-bin/wa?A1=ind1208a&L=sas-l#31

[Hawkeye]

scandate or usedate?

[Reeza]

scandate is for excel and usedate is for access I believe.

[Hawkeye]

no usedate is for .xlsx

[Reeza]

apparently usedate...

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003103761.htm

[Hawkeye · Accepted solution]

Well I am not really sure what happened but it worked, here is what I ended up using.

proc import out=work.data
        datafile =  'filepath'
        DBMS = EXCEL Replace; 
    RANGE="Sheet1$"; 
    getnames = yes; 
    mixed = no;
    scantext = yes;
    usedate = yes; 
    scantime = no;
run;

I am really confused because it wouldn't run, I got up to walk around and clear my head.  Ran it again without changing anything and it worked.