topic Re: Reading SAS Table and passing values to a macro variable in SAS Procedures

Reading SAS Table and passing values to a macro variable

pr1 — Mon, 20 Apr 2015 19:11:36 GMT

I have this import statement:

PROC IMPORT DATAFILE = 'C:\ Data Files\AAAAAA\DN txns BBBBBB Test.xlsx'

DBMS=XLSX

OUT = WORK.AP_XXXXXXXX;

SHEET = 'Sheet1';

I have some 50 of such import statements in my code.

In the above statement AAAAAA, BBBBBB, XXXXXXXX comes from one SAS table with three columns which contains these values.

Is it possible to read these fields from SAS table and pass the value to the above statement and make it execute 50 times?

Thanks.

Re: Reading SAS Table and passing values to a macro variable

Haikuo — Mon, 20 Apr 2015 20:11:41 GMT

Time for you to get hands on CALL EXECUTE().

Haikuo

Re: Reading SAS Table and passing values to a macro variable

asdfghjkl__lpoi87654ewasxcv — Wed, 22 Apr 2015 12:36:54 GMT

I would use select into in a proc sql to read all the rows into enumerated macro vars (e.g. select AAAAAA into :a1-:a50) then use an iterated macro do loop to step through the macro vars and perform the import step. Since there are no do loops allowed in open code you would need to put this in a macro

%do i=0 %to 50;

PROC IMPORT DATAFILE = 'C:\ Data Files\&&a&i.\DN txns &&b&iTest.xlsx'

DBMS=XLSX

OUT = WORK.AP_&&x&i;

SHEET = 'Sheet1';

%end;

Re: Reading SAS Table and passing values to a macro variable

pr1 — Wed, 22 Apr 2015 12:44:50 GMT

Thanks!! That works.