topic Re: Macro code for data import of multiple excel files in SAS Programming

Macro code for data import of multiple excel files

amb4kw — Tue, 19 Apr 2022 15:07:56 GMT

I am trying to export data from a clinical database (OnCore) and analyze it in SAS. The database exports data as multiple excel files rather than one large dataset. This macro code is supposed to import all the the raw excel files into SAS and I cannot figure out why there is an error message within my code. It seems to have an issue when I specify the library name "sasd" for the file name.

%MACRO formnames();
%DO i=1 %TO &numforms;
data _null_;
set formnames;
if n= &i;
call symput('Oncore', TRIM(Oncore));
call symput('SAS', TRIM(SAS));
run;
%put &Oncore;
PROC IMPORT OUT= sasd._raw_&sas
 DATAFILE= "&sasd\&oncore.xlsx"
 DBMS=XLSX REPLACE;
 SHEET="&Oncore";
 GETNAMES=YES;
RUN;
%END;
%MEND;
%formnames();

Re: Macro code for data import of multiple excel files

PaigeMiller — Tue, 19 Apr 2022 15:17:06 GMT

We need to see ALL of the log for this macro. Please do not show us parts of the log.

Also, please copy the log (ALL of it for this macro) as text, not as a screen capture, and then paste it into the window that appears when you click on the </> icon.

Also, if you search the SAS Communities, you will most likely find working code to import multiple excel files, you shouldn't have to write your own code for this.

Re: Macro code for data import of multiple excel files

Tom — Tue, 19 Apr 2022 15:26:52 GMT

Your input dataset, formnames, probably does not have a variable named SAS. Or if it does it is numeric and has missing values.

That is why this line put eleven spaces followed by a period into the macro variable SAS.

call symput('SAS', TRIM(SAS));

PS Do not use the older CALL SYMPUT() function (unless you really need to insert leading or trailing spaces into your macro variables). Use the modern (less than 40 years old) CALL SYMPUTX() function instead.

data _null_;
  set formnames(firstobs=&i obs=&i);
  call symputx('Oncore', oncore);
  call symputx('SAS', sas);
run;

Re: Macro code for data import of multiple excel files

ballardw — Tue, 19 Apr 2022 15:29:43 GMT

This is at least one major probl

 DATAFILE= "&sasd\&oncore.xlsx"

The macro processor using the . to indicate the end of a macro variable when combining with other text. So if oncore were "filename" then &oncore.xlsx would resolve to "filenamexlsx" without the dot for the extension.

So that piece of code needs to be

 DATAFILE= "&sasd\&oncore..xlsx"

Your messages would make me think that for one or more records in the data set Formnames that ONCORE is missing.

If your code actually does read

if n= &i;

and you expect to get the result from the i numbered record in the data set I hope that you have a variable named n.

You may also want to use Call SYMPUTX instead of Symput to remove leading and trailing blanks that might occur with default .

If you have a bunch of names in a data set then look at CALL EXECUTE to generate lines of code from the data set instead of looping around record numbers of a data set that way.

Re: Macro code for data import of multiple excel files

Reeza — Tue, 19 Apr 2022 17:35:25 GMT

If you have a data set with those variables using CALL EXECUTE is easier.

data test;

set formnames;

str = catt('proc import out=sasd._raw_', sas, 
 ' datafile= "',
  sas, 
  'd\', 
   oncore ,
   '.xlsx"', 
  'dbms=xlsx replace;',
   'Sheet = "',
   oncore,
    '";',
'Getnames = YES; run;'
  );

*call execute(str);
run;
length str $10000.;

Run this data set and make sure the code generated in the STR variable is the correct PROC IMPORT.

Then once you're sure the code is correct, you can uncomment the CALL EXECUTE and that will run all the proc imports in the data step.

If you decide to continue with macro logic, look at this line.

 DATAFILE= "&sasd\&oncore.xlsx"

It's attempting to reference a macro variable &sasd

If you want it to use &sas you need to add a period after the sas to let SAS know that's the end of the macro variable, as well as add another period to separate the .XLSX portion.

 DATAFILE= "&sas.d\&oncore..xlsx"

@amb4kw wrote:

I am trying to export data from a clinical database (OnCore) and analyze it in SAS. The database exports data as multiple excel files rather than one large dataset. This macro code is supposed to import all the the raw excel files into SAS and I cannot figure out why there is an error message within my code. It seems to have an issue when I specify the library name "sasd" for the file name.
%MACRO formnames();
%DO i=1 %TO &numforms;
data _null_;
set formnames;
if n= &i;
call symput('Oncore', TRIM(Oncore));
call symput('SAS', TRIM(SAS));
run;
%put &Oncore;
PROC IMPORT OUT= sasd._raw_&sas
 DATAFILE= "&sasd\&oncore.xlsx"
 DBMS=XLSX REPLACE;
 SHEET="&Oncore";
 GETNAMES=YES;
RUN;
%END;
%MEND;
%formnames();