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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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