topic Re: PROC IMPORT - Problems with xls and xlsx in SAS Procedures

PROC IMPORT - Problems with xls and xlsx

GuiVtzl — Thu, 19 Sep 2013 09:46:16 GMT

Hello everyone,

I've got a little problem with my proc import. Indeed, when I use the code below, it doesn't enable me to import .xlsx files, but only .xls

How could I change it to import both ?

Thanks

Here's my code :

PROC IMPORT OUT=TRAVAIL.INPUTS_1_&Q&Y

DATAFILE= "Y:\&Q&Y."

DBMS=EXCEL REPLACE;

RANGE="Sheet1$";

GETNAMES=YES;

MIXED=NO;

SCANTEXT=YES;

USEDATE=NO;

SCANTIME=YES;

Re: PROC IMPORT - Problems with xls and xlsx

yaswanthj — Thu, 19 Sep 2013 10:21:41 GMT

Hi..

Convert .xlsx (2007) file to .xls (2003), in your computer and use your code. you can easily import the files..

Thanks,

Yaswanth

Re: PROC IMPORT - Problems with xls and xlsx

yaswanthj — Thu, 19 Sep 2013 11:28:16 GMT

Hi..

You can convert the .xlsx file to .csv and using infile statement, you can easily get into sas environment without using proc import procedure.

data test;

infile '"path/file.csv";

run;

Re: PROC IMPORT - Problems with xls and xlsx

Anotherdream — Thu, 19 Sep 2013 13:39:19 GMT

Along yaswanthj line of thinking, you could even write sas code to convert your Excel file to a csv without using an import, and then read in the csv file.

Re: PROC IMPORT - Problems with xls and xlsx

riteshvado — Thu, 19 Sep 2013 15:14:16 GMT

Try DBMS=EXCELS instead of EXCEL and then try.

SAS/ACCESS(R) 9.2 Interface to PC Files: Reference, Second Edition

Re: PROC IMPORT - Problems with xls and xlsx

JohannesLang — Thu, 19 Sep 2013 15:26:17 GMT

Hello GuiVtzl,

the values for the DBMS options that you can use depend on your SAS version (including the "bitness", i.e. if you have 32bit or 64bit SAS).

If you have SAS 9.3 64bit maintenance release 1 (TS1M0), you can use either DBMS=XLS or DBMS=XLSX, but you have to decide which Excel format you want to support, since they are quite different.

If thats not possible, you probably have to go the CSV way like the other commentators suggested.

regards,

Johannes

Re: PROC IMPORT - Problems with xls and xlsx

andreas_lds — Tue, 24 Sep 2013 07:24:57 GMT

You can extract the file extension using %let fileExtension = %scan(&excelFile, -1, %str(.)) and use it afterwards in the dbms option.