How to create a macro to import multiples workbooks with different name into SAS PC?

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

How to create a macro to import multiples workbooks with different name into SAS PC?

I have multiple workbooks (xls) with different name i am trying to import into SAS PC. i am using proc import multiple time to do the job.

Workbooks name are : Soccer, Tennis and Golf saved in: H:\user\document\sas

I created a library name Mywork

Help to use a macro?

Thanks


Accepted Solutions
Solution
‎01-16-2015 12:59 PM
Super User
Posts: 19,167

Re: How to create a macro to import multiples workbooks with different name into SAS PC?

%macro import_xls(input, output);

proc import datafile="H:\user\document\sas\&input..xls" out=mywork.&output DBMS=xls; run;

%mend;

%import_xls(soccer, soccer);

%import_xls(golf, golf);

View solution in original post


All Replies
Super User
Super User
Posts: 7,720

Re: How to create a macro to import multiples workbooks with different name into SAS PC?

Hi,

Well, probably the quickest way is to do a libname to the Excel file.  You can then access the various sheets as datasets.  Here is some guidance: http://www2.sas.com/proceedings/sugi31/024-31.pdf

Contributor
Posts: 46

Re: How to create a macro to import multiples workbooks with different name into SAS PC?

the pdf suggested has good stuff but that s how i did. I thought there is macro way to do that when you have multiple excel files to import from external.


PROC Star
Posts: 7,437

Re: How to create a macro to import multiples workbooks with different name into SAS PC?

It would help if you showed the proc import code you used to successfully import the three workbooks.

Super User
Super User
Posts: 7,720

Re: How to create a macro to import multiples workbooks with different name into SAS PC?

Yes, post your code.  What do you mean by "thought there was a macro way to do it"?  You can put most code you like in a macro, but there is nothing special about macros, to all intents and purposes it is just a find and replace system to save you typing the same code over and over.  If you know the sheetnames, then you could put your import into a macro with sheetname="&macro variable." and call it once per sheet.  Is there much value though?

Solution
‎01-16-2015 12:59 PM
Super User
Posts: 19,167

Re: How to create a macro to import multiples workbooks with different name into SAS PC?

%macro import_xls(input, output);

proc import datafile="H:\user\document\sas\&input..xls" out=mywork.&output DBMS=xls; run;

%mend;

%import_xls(soccer, soccer);

%import_xls(golf, golf);

Contributor
Posts: 46

Re: How to create a macro to import multiples workbooks with different name into SAS PC?

thanks Reeza it works

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 396 views
  • 0 likes
  • 4 in conversation