topic Re: How to Execute a macro in loop with arguments in SAS Programming

How to Execute a macro in loop with arguments

Lakshmisas — Wed, 14 Nov 2018 03:40:29 GMT

Hi All,

Can anyone suggest me in executing a macro for each row in the dataset , passing arguments dynamically as given below.

Suggesting a solution to me is hifhly appreciated.

----------------------------------------------------------------

Read Data1 dataset with Columns "Company" and "DSName"

eg: Company DSName

Comp1 DS1

comp2 DS2

-------------------------------------------------------

data _null_;
set Work.Data1;
length args1 $300. args2 $100.;

args1 = cats("'C:\Test\",Company,".xlsx'");
args2 = cats(DSName,'_2018');

put "args1=" args1;
put "args2=" args2;

call execute(cats('%nrstr(%SasDS(',args1,args2,'));'));

run;

-------------------------------------------------------

%let Qtr=Q1;

%macro SasDS(Name,Dss);

libname Perm&&Qtr 'C:\Test\SasDS\&&Qtr';

PROC IMPORT OUT=Perm&&Qtr.&DSS.
DATAFILE=&Name.
DBMS=EXCEL REPLACE;
SHEET="&&Qtr";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

%mend SasDS;

Re: How to Execute a macro in loop with arguments

Tom — Wed, 14 Nov 2018 03:51:52 GMT

Did it not work? What error did you get?

Make sure to compile the macro before running the data step that pushes calls to it onto the stack via CALL EXECUTE().

Re: How to Execute a macro in loop with arguments

mkeintz — Wed, 14 Nov 2018 04:45:56 GMT

The call execute statements will cause the embedded commands to be executed, in sequence, immediately after the data step. Since the code to be executed includes use of the macro SASDS, that macro must be defined prior to the data step execution.

Perhaps, it's just the way you described your question, but it appears that you don't define the SASDS macro until after the data step.

If that's not the case, then as @Tom says, please show us what happened that you didn't expect.

Re: How to Execute a macro in loop with arguments

Kurt_Bremser — Wed, 14 Nov 2018 07:30:08 GMT

Your macro expects two arguments separated by a comma, but your call execute does not supply that comma. Expand the call execute:

call execute(cats('%nrstr(%SasDS(',args1,',',args2,'));'));

Re: How to Execute a macro in loop with arguments

Lakshmisas — Thu, 15 Nov 2018 10:08:10 GMT

Hi ,

Thanks for your prompt response .

Issue got fixed by placing comma between args1 and args2.

call execute(cats('%nrstr(%SasDS(',args1,',',args2,'));'));