sas macros

[nayab_shaik]

Hi Team,

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

[VinitvictorCorr]

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

[Kurt_Bremser]

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.).

[Shmuel]

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.

[s_lassen]

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.

[Kurt_Bremser]

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