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

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

Armand — Thu, 15 Jan 2015 16:45:15 GMT

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

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

RW9 — Thu, 15 Jan 2015 16:49:16 GMT

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

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

Armand — Fri, 16 Jan 2015 16:33:45 GMT

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.

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

art297 — Fri, 16 Jan 2015 16:43:44 GMT

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

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

RW9 — Fri, 16 Jan 2015 17:28:15 GMT

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?

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

Reeza — Fri, 16 Jan 2015 17:59:11 GMT

%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);

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

Armand — Mon, 19 Jan 2015 18:44:25 GMT

thanks Reeza it works