topic Re: how can I make SAS import more than 255 Variables from EXCEL 2007 at a time? in SAS Programming

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

Dingdang — Thu, 07 Nov 2013 14:09:08 GMT

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

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

SASKiwi — Thu, 07 Nov 2013 19:26:49 GMT

Have you tried DBMS = Excel2007 or DBMS = XLSX?

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

MumSquared — Thu, 07 Nov 2013 21:32:50 GMT

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

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

Dingdang — Fri, 08 Nov 2013 07:48:01 GMT

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

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

LinusH — Fri, 08 Nov 2013 17:21:36 GMT

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

Have you tried the LIBNAME EXCEL engine?

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

MumSquared — Mon, 11 Nov 2013 23:57:06 GMT

I'm on 9.3

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

LinusH — Tue, 12 Nov 2013 11:39:26 GMT

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