BookmarkSubscribeRSS Feed
sasbegginer
Calcite | Level 5
Hi,

I have about 10 excel files with the same column heading and i need a way to import all off them in one table (appending one after another). I can write a proc import statement for each one followed by a proc append statement but how would i do it all together? Thanks
19 REPLIES 19
LinusH
Tourmaline | Level 20
Well, as you described:

PROC IMPORT ....

PROC APPEND

PROC IMPORT ....

PROC APPEND
etc.

If you want to save some programming line you could wrap it into a macro.

/Linus
Data never sleeps
sasbegginer
Calcite | Level 5
Yes but any help writing a macro which doesnt require the file name and imports all the files in a folder...
LinusH
Tourmaline | Level 20
See this as strictly for inspiration, totally untested. See on-.line doc for references.

%macro importAll(DIR = );

%let rc = %sysfunc(filename(dir,&DIR));
%let did = %sysfunc(dopen(dir));
%let nfiles = %sysfunc(dnum(did));

%do I = 1 to &NFILES;

%let ExcelFile = %sysfunv(dread(did,&I));

proc import file="&DIR\&ExcelFile." out=ExcelData. ...;
...
run;

proc append base=AllData data=ExcelData;
run;

%end;

%let rc = %sysfunc(dclose(did));
%let rc = %sysfunc(filename(dir));

%mend importAll;

%importAll(dir=C:\data\MyExcelFiles);

/Linus
Data never sleeps
Peter_C
Rhodochrosite | Level 12
Any way of using data step infile option FILENAME= and *.xls as the file to collect the file names without reading through the whole file?
Then the file names become available for a call execute invocation of PROC import for each workbook.
just an interesting thought. ....
Oleg_L
Obsidian | Level 7
Peter,
your interesting thought is working.
Thank you for the idea.

[pre]

%macro sks2sas01(input=d:\work\test1,out=work.tt);
/* 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 ;
RANGE="PRV_RATE$A1:F65536";
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;

%sks2sas01(input=d:\work\test1,out=work.tt);


[/pre]
Dingdang
Fluorite | Level 6

hi Oleg,

your code looks great but í've got a small problem running it (since i am an absolute beginner in sas, i dont really fully understand your code as to say where my problem is). i have a file with 10 test data tables and there is only one table in each of them, containing the variables a b c d...n and each variable has 30 random values.  I suppose RANGE gives the area where the data should be read, so i adjusted my code to 'RANGE="table1$A1:N30"' otherweise i didnt change any part of the code. and then i ran it and got the following warning message:

 

WARNING: Variable a was not found on BASE file. The variable will not be added to the BASE file.

and this for every variable a to n. As a result contains work.tt no columns.

why did this happen and how should i adjust my code so it works?

Many thanks again!

BR  Dingdang

Dingdang
Fluorite | Level 6

HI Oleg,

problem solved as i changed RANGE to SHEET. but many thanks anyway Smiley Happy

BR  Dingdang

Dingdang
Fluorite | Level 6

Hi Oleg,

i used your code on another file. the excelsheets that i want to read into SAS have about 5000 variables and each of them have only 1 Observation. You code worked great but only 255 Variables were read into SAS, and each of them had then 8 observations (only the first one was originally in my excelsheets and the other 7 are just empty ones). why did this happen? how can i correct it so that all the variables could be correclty read into SAS?

thanks again for your super help!!

BR  Dingdang

Dingdang
Fluorite | Level 6


ok. found the problem. SAS can only import and export 255 variable when you access excel 2007 files. is it possible that i modify the code, so that SAS read 255 variables in once at a time and then merge them all together in the end?

Many thanks. BR  Dingdang

art297
Opal | Level 21

: You really ought to create a new discussion rather than just ask a new question as a reply to an old discussion.  In that new discussion you really ought to describe your problem better, specifically to show the code you are using.  SAS can read all columns from an Excel 2007 workbook.

Oleg_L
Obsidian | Level 7
You can try this example too.

[pre]

%macro w2sas05(input=d:\dep\input\plast\2009\w4_2,out=work.plast);



/* 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)));
drop myfiles;
run;
filename myfiles clear;

%let j=1;
%let dsid=%sysfunc(open(list1));
%let rc=%sysfunc(fetch(&dsid));
%do %while(&rc=0);
%let file=%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,fname))));




PROC IMPORT DBMS=EXCEL2002 OUT= _&j
DATAFILE= &file REPLACE ;
GETNAMES=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
DBSASLABEL=NONE;
TEXTSIZE=100;
RUN;



proc append data=_&j base=&out; run;

proc delete data=_&j; run;

%let rc=%sysfunc(fetch(&dsid));
%let j=%eval(&j+1);
%end;
%let rc=%sysfunc(close(&dsid));

%mend w2sas05;

[/pre] Message was edited by: Oleg_L
HamidK
Calcite | Level 5
Oleg,

Great macro.

It works fine in native SAS 9.2 but I get the following error messages when running it on Enterprise Guide 4.3:

ERROR: Insufficient authorization to access PIPE.
ERROR: Error in the FILENAME statement.

Any idea anyone what I can do to make it work in Enterprise Guide? Here is the modified macro code I am using:

%macro w2sas05(input=c:\Data\SAStests\ScrapTies\, out=work.DailyTemp);
* 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)));
drop myfiles;
run;
filename myfiles clear;
%let j=1;
%let dsid=%sysfunc(open(list1));
%let rc=%sysfunc(fetch(&dsid));
%do %while(&rc=0);
%let file=%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,fname))));

PROC IMPORT DBMS=EXCEL OUT= _&j
DATAFILE= &file REPLACE ;
SHEET="summary";
GETNAMES=YES;
SCANTEXT=YES;
USEDATE=NO;
SCANTIME=NO;
DBSASLABEL=NONE;
TEXTSIZE=100;
RUN;
proc append force data=_&j base=&out;
run;
proc delete data=_&j;
run;
data &out;
set &out;
run;
%let rc=%sysfunc(fetch(&dsid));
%let j=%eval(&j+1);
%end;
%let rc=%sysfunc(close(&dsid));
%mend w2sas05;

%w2sas05;
SASKiwi
PROC Star
Your macro executes an operating system DIR command to get a list of files. By default this ability to execute an operating system command is turned off in EG. A change to your SAS server options would be required, for example I suspect XCMD = OFF.
HamidK
Calcite | Level 5
SASkiwi,

How can I change the server options in EG? I did not find anything under >Tools>Options.

Thanks.

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
  • 19 replies
  • 15758 views
  • 1 like
  • 10 in conversation