how can I make SAS import more than 255 Variables from EXCEL 2007 at a time?

[Dingdang]

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

[SASKiwi]

Have you tried DBMS = Excel2007 or DBMS = XLSX?

[MumSquared]

I can load 365 variables using dbms=xls for an excel 2007 .xls file and dbms=xlsx for excel 2010 file

[Dingdang]

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

[LinusH]

I think that the DBMS=XLSX was implemented in 9.3. Time for an upgrade...?

Have you tried the LIBNAME EXCEL engine?

[MumSquared]

I'm on 9.3

[LinusH]

Ok...? My respone was to ...