topic Re: sas macros in SAS Programming

sas macros

nayab_shaik — Tue, 17 Sep 2019 05:40:34 GMT

Hi Team,

i have different name of excel files how to import all excel files at a time by using macros

Re: sas macros

VinitvictorCorr — Tue, 17 Sep 2019 06:21:27 GMT

it depends on what are the names of these excel files.

Re: sas macros

Kurt_Bremser — Tue, 17 Sep 2019 06:30:42 GMT

As you have already been told multiple times, use descriptive subject lines!

Start with successfully importing one Excel file. Then look what needs to be made dynamic, and replace that with macro variables. Finally, look for a way to create those macro variables automatically, by determining from how your files are stored (single vs. multiple directories etc.).

Re: sas macros

Shmuel — Tue, 17 Sep 2019 06:32:19 GMT

Clarify:

1) Are all excel files in the same folder or in different folders?
Are their names all in same format? (differ by suffix? date? something else?)

2) Are all excel files of same type: .xls or .xlsx and which of them ?

3) Are all excel files with same columns format?
Do you wand to import all into one SAS dataset or each in its own one?

All those info affect how to code the macro.

Re: sas macros

s_lassen — Tue, 17 Sep 2019 07:10:19 GMT

For a thing like that, I would not use macros. I think it is easier and safer to write a program to a temporary file, and then %INCLUDE that, e.g.:

filename dirlist pipe 'dir c:\myfiles\*.xlsx /b';

filename tempsas temp;
data _null_;
  infile dirlist;
  input;
  filename=_infile_;
  put "Proc import FILE='c:\myfiles\" filename +(-1) "'"/
        '  DBMS=EXCEL5 REPLACE out=Excel_Imp' _N_ ';' /
        '  GETNAMES=yes;' /
        'RUN;'
        ;
run;

%include tempsas;

Before submitting the %include statement, you can open the TEMPSAS file in an editor window, see if the generated code looks right, and try submitting one of the generated PROC IMPORT statements, to test if it works.

Re: sas macros

Kurt_Bremser — Tue, 17 Sep 2019 11:03:32 GMT

BTW, a google search for "sas communities reading multiple excel files" will reveal the many, many times this subject has been covered before.