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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.