Hi,
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 ;
run;
%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);
data list1;
length fname $256.;
infile myfiles truncover;
input myfiles $100.; /* put infile;*/
fname=quote(upcase(cats("&input",'\',myfiles)));
out="&out";
drop myfiles;
call execute('
PROC IMPORT DBMS=EXCEL2002 OUT= _1
DATAFILE= '||fname||' REPLACE ;
SHEET="Tabelle1$";
GETNAMES=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
DBSASLABEL=NONE;
TEXTSIZE=100;
RUN;
proc append data=_1 base='||out||' force; run;
proc delete data=_1; run; ');
run;
filename myfiles clear;
%mend sks2sas01;
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:
http://support.sas.com/kb/37/612.html
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!
BR Dingdang
Have you tried DBMS = Excel2007 or DBMS = XLSX?
I can load 365 variables using dbms=xls for an excel 2007 .xls file and dbms=xlsx for excel 2010 file
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?
BR Dingdang
I think that the DBMS=XLSX was implemented in 9.3. Time for an upgrade...?
Have you tried the LIBNAME EXCEL engine?
I'm on 9.3
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.