11-07-2013 09:09 AM
i asked this question once before but didnt formulate it that clearly. Just arrived back from holidays and would like to give it a second shot
I am using the following code from Oleg to import excel 2007/2010 tables with the same structure from a file into sas.
%let dirname = R:\test;
filename DIRLIST pipe "dir /B &dirname\*.xlsx";
data libl.dirlist ;
length fname $256;
infile dirlist length=reclen ;
input fname $varying256. reclen ;
%macro sks2sas01(input=d:\work\test1,out=libl.testt); /* read files in directory */
%let dir=%str(%'dir %")&input.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&dir);
length fname $256.;
infile myfiles truncover;
input myfiles $100.; /* put infile;*/
PROC IMPORT DBMS=EXCEL2002 OUT= _1
DATAFILE= '||fname||' REPLACE ;
proc append data=_1 base='||out||' force; run;
proc delete data=_1; run; ');
filename myfiles clear;
It works great. the only problem is that, sas only imports 255 variables and actually i have many more in my excel tables. I looked up this problem in internet and I found the following link from SAS:
According to this, i would probably need to save the excel files to text files first and then do the import. Is there any way that i can modify the code before and let SAS read all the variables into a dataset? or maybe with a loop, so that 255 variables at a time and then merge the datasets into one?
I am very grateful for any tipps and help!
11-07-2013 04:32 PM
I can load 365 variables using dbms=xls for an excel 2007 .xls file and dbms=xlsx for excel 2010 file
11-08-2013 02:48 AM
HI SASKiwi and MumSquared,
thank you both for your answers. I am using SAS 9.2 and I tried with both statements, but I got the error message saying that DBMS=XLSX not valid for proc import, which I dont quite understand. I read some old posts and this should be able to work in SAS 9.2. which version are you using?
11-08-2013 12:21 PM
I think that the DBMS=XLSX was implemented in 9.3. Time for an upgrade...?
Have you tried the LIBNAME EXCEL engine?