BookmarkSubscribeRSS Feed
Dingdang
Fluorite | Level 6

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

6 REPLIES 6
MumSquared
Calcite | Level 5

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

Dingdang
Fluorite | Level 6

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
Tourmaline | Level 20

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

Have you tried the LIBNAME EXCEL engine?

Data never sleeps
LinusH
Tourmaline | Level 20

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

Data never sleeps

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 7465 views
  • 6 likes
  • 4 in conversation