DATA Step, Macro, Functions and more

need a macro to read excel tables with similar names in a certain file into sas

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

need a macro to read excel tables with similar names in a certain file into sas

hi all,

i am a beginner in sas and wondering if it is possible to write a macro to read external data like excel tables with similar names (eg. reports_a, reports_b, reports_c etc. ) which i saved in a certain file into sas. I wrote sth. like

%macro imp (path, name, n);

%Do i=1 %To &n;

proc import out=libl.test&i;

Datafile="&path.&name.&i..xls";

sheet="Table1";

run;

%end;

%mend imp

It doesnt look so good. Can someone give me some tips how i can do this? plus, actually i dont want to give n as a macro variable, cause i probabably dont know how many tables are there (of course i can check the file itself and get to know n myself, but that is not so schick, and everytime i got new reports coming in, this number is going to change), is it possible that sas does a loop and read the tables one by one until no tables are left unread? and is it possible that i save my sas dataset with the index from the original excel tables, like in my example a, b, c etc.? so that when i look at the name of the sas data sets, i know already exactly from whom this report comes from, from person a, b oder c and so on.

I am very grateful for any kind of help and suggestions!

BR  Dingdang


Accepted Solutions
Solution
‎09-26-2013 06:54 AM
Regular Contributor
Posts: 151

Re: need a macro to read excel tables with similar names in a certain file into sas


All Replies
Super User
Posts: 5,260

Re: need a macro to read excel tables with similar names in a certain file into sas

You can first read the the contents of a directory (and stor it in a work table), and "pipe" all Excel file names into your macro instead, i.e. by using the call execute() construct.

Data never sleeps
Contributor
Posts: 57

Re: need a macro to read excel tables with similar names in a certain file into sas

Hi LinusH

thanks for your prompt answer. I cannt really understand your method (have only one week experience with sas Smiley Sad). can you probably give me an example code, how this is going to look like?

would be very thankful for that.

Dingdang

Super User
Posts: 5,260

Re: need a macro to read excel tables with similar names in a certain file into sas

Ok, this is maybe the first week kind of logic, but you'll probably learn something anyhow...

You can either use filename pipe to issue a OS dir command - which you can call in a data step.

meta code:

filename myDir pipe 'dir search-path';

data _null_;

infile myDir;

input fileName input_spec;

if subsetting;

call execute('%imp(' || fileName || ');');

run;

Data never sleeps
Contributor
Posts: 57

Re: need a macro to read excel tables with similar names in a certain file into sas

hi LinusH,

thanks for your code. In combination with Oleg's code i think i understand better how directory and call execute works Smiley Happy  very useful tips!

BR  Dingdang


Solution
‎09-26-2013 06:54 AM
Regular Contributor
Posts: 151

Re: need a macro to read excel tables with similar names in a certain file into sas

Contributor
Posts: 57

Re: need a macro to read excel tables with similar names in a certain file into sas

hi Oleg,

thanks for your answer. this is exactly what i am looking for Smiley Happy

BR  Dingdang

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 476 views
  • 3 likes
  • 3 in conversation