Importing 2 datafiles using sas macro

[aayo]

Hi,

I am new to sas macro and I have been trying to use it to import 2 datafiles but seems I am missing a line.

Below is my code. a1 and a2 are the 2 datafiles. Data_file1 &2 are the file names. Data_dir is the file path for both data. 2 datasets were imported as my output but contain same data (a1).

Kindly help.

%macro one (a1, a2);
proc import out= &a1 &a2
datafile ="&data_dir\&data_file1"
dbms=xls replace;
getnames=yes;
run;
%mend one;

%one (&library..&a1);
%one (&library..&a2);

[Tom]

So first you have four undefined macro variables in your little program. Two in the macro definition: DATA_DIR and DATA_FILE1. And two in the calls to the macro: LIBRARY and A1.

Second both calls are going to read from the same source file since that part of the macro doesn't depend on the input parameters, A1 and A2.  Instead it only depends on the values of those two "magic" macro variables.  Magic macro variables is my term for macro variables that are referenced in the middle of a program without any indication of where the values coming from. The get values magically.

And finally you are calling the macro but are only supplying a value for one of the two input parameters.  But fortunately that doesn't cause any trouble since when the second parameter is empty it just means that there isn't an extra word in the PROC IMPORT statement between the value of the OUT= option and the start of the DATAFILE= option.

Try something like this instead?

%macro one (filename, dataset);
proc import out= &dataset
  datafile =&filename
  dbms=xls replace
;
  getnames=yes;
run;
%mend one;

%let data_path=C:\myfiles ;
options mprint;
%one (filename="&data_path\file1.xls",dataset=work.file1);
%one (filename="&data_path\file2.xls",dataset=work.file2);

Turning on the MPRINT option will let you see what code the macro ends up generating in the log.

[aayo]

Hi Tom,

I defined my macros before but didn't post it here. I have tried your suggestion but I still don't understand the first line. Here is my full code now.

%let data_dir = %str(C:\Users\aayo....);
%let data_file1 = %str(x); 
%let data_file2 = %str(y); 
filename data "&data_dir";
%let a1 = x; /*set name of sas dataset*/
%let a2 = y; /*set name of sas dataset*/
%let c=z;
%let library = cwork; /* set name of user library */
libname &library "&data_dir";

%macro one (x, y);
proc import out= &a1 &a2
datafile ="&data_dir"
dbms=xls replace;
getnames=yes;
run;
%mend one;
%one (&data_dir\&data_file1..xls,&library..&a1);
%one (&data_dir\&data_file2..xls,&library..&a2);

[Kurt_Bremser]

Go back to square 1 of the macro development path: start with working SAS code without any macro elements.

Please post that code here, and we'll show you how to get from static code to dynamic code in a few steps that are guaranteed to always work.

[aayo]

Hi Kurt,

below is my working code but i believe i should be able to import the 2 files in one macro statement.

PROC IMPORT OUT= WORK.x 
DATAFILE= "C:\Users\aayo\x.xls"
DBMS=EXCEL REPLACE;
RANGE="Sheet1$";
GETNAMES=YES;
MIXED=NO;
RUN;
PROC IMPORT OUT= WORK.y
DATAFILE= "C:\Users\aayo\y.xls"
DBMS=EXCEL REPLACE;
RANGE="Sheet1$";
GETNAMES=YES;
MIXED=NO;
RUN;

[Kurt_Bremser · Accepted solution]

Step one: identify the parts of the code that need to be dynamic; in your case, it is the name of the Excel file and the name of the dataset, everything else is the same. And the file- and dataset-name are identical, so we can use the same macro variable for both

So we replace these with a macro variable, and set it:

%let name=x;

proc import
  out=work.&name. 
  datafile="C:\Users\aayo\&name..xls"
  dbms=excel
  replace
;
range="Sheet1$";
getnames=yes;
mixed=no;
run;

Note the additional dot in the filename; it is needed to terminate the macro variable reference.

Once this is tested and verified to work, wrap it into a macro definition, with the macro variable(s) as parameter(s):

%macro import_one(name);
proc import
  out=work.&name. 
  datafile="C:\Users\aayo\&name..xls"
  dbms=excel
  replace
;
range="Sheet1$";
getnames=yes;
mixed=no;
run;
%mend;

and call this repeatedly:

%import_one(x)
%import_one(y)

If you have a list of parameters for the macro stored in a dataset, you can automate the calls:

data list;
input name $;
datalines;
x
y
;

data _null_;
set list;
call execute(cats('%nrstr(%import_one(',name,'))'));
run;

The %nrstr() wrapper is used to prevent macro-timing problems and premature execution of pure macro code.

[aayo]

Hi Kurt,

Thanks for your help.

However, I have 2 different files x (110 obs) and y (236 obs). This code gives me 2 datasets with x attributes and that is where my issue is. how do I state the second filename? Below is the log for clearer picture.

234 %one (&a1);

NOTE: The import data set has 110 observations and 8 variables.
NOTE: WORK.X data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

235 %one (&a2);

NOTE: The import data set has 110 observations and 8 variables.
NOTE: WORK.Y data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

[Kurt_Bremser]

Well, the two macro calls obviously created the same result (2 datasets, x and y) that your non-macro code achieved. Where is the problem?

[aayo]

Below is my non-macro log.

287 PROC IMPORT OUT= WORK.x
288 DATAFILE= "C:\Users\aayo\x.xls"
290 DBMS=EXCEL REPLACE;
291 RANGE="Sheet1$";
292 GETNAMES=YES;
293 MIXED=NO;
294 RUN;

NOTE: WORK.x data set was successfully created.
NOTE: The data set WORK.x has 110 observations and 8 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 1.48 seconds
cpu time 0.37 seconds

298 PROC IMPORT OUT= WORK.y
299 DATAFILE= "C:\Users\aayo\y.xls"
301 DBMS=EXCEL REPLACE;
302 RANGE="Sheet1$";
303 GETNAMES=YES;
304 MIXED=NO;
305 RUN;

NOTE: WORK.y data set was successfully created.
NOTE: The data set WORK.y has 263 observations and 8 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.37 seconds
cpu time 0.21 seconds

The missing line in the macro I believe is specifying the 2nd filename.

[Kurt_Bremser]

Compare your macro code to mine. You will spot the difference.

[aayo]

Hi Kurt,

I got it now and I think I am getting to understand how macro works to reduce repetitive codes.

Thanks for your patience.

Cheers!

[Kurt_Bremser]

The most important think to keep in mind: macro is a code generator, so the code to be created has to be at the center of the coder's attention.