topic Re: Calling a macro using RSubmit in SAS Programming

Calling a macro using RSubmit

Philmingo — Thu, 31 Aug 2023 16:28:54 GMT

I am working on a project that requires pulling several years of data. I am doing a simplified test of how to use RSubmit and call a macro that can cycle through 2 year (my project requires 6 years), so they can run in parallel.

When I run this I get a log message that says:

WARNING: Apparent invocation of macro TEST1 not resolved.

1 %TEST1(&YEAR2.);
-
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

How do I pass the macro TEST1 through to each RSubmit? Also, I assume that there will be issues calling YEAR1 and YEAR2. Thanks for the help!

options autosignon=yes

noconnectwait

noconnectpersist

sascmd='!sascmd'

compress=yes;

%LET YEAR1 = 2021;

%LET YEAR2 = 2022;

%MACRO TEST1(YEAR);

PROC SQL;

CREATE TABLE workdir.CHECKTOTALS_&YEAR. AS SELECT

DATE_OF_SERVICE,

SUM(Scripts) as Scripts

FROM SOURCE_TABLE

Where SERVICEMONTH >= &YEAR.01 and SERVICEMONTH <= &YEAR.12

GROUP BY 1

ORDER BY 1

;QUIT;

%MEND TEST1;

%syslput _USER_ / remote=worker1;

%syslput _USER_ / remote=worker2;

libname workdir "%sysfunc(getoption(work))";

rsubmit inheritlib=(workdir) remote=worker1;

%TEST1(&YEAR1.);

ENDRSUBMIT;

rsubmit inheritlib=(workdir) remote=worker2;

%TEST1(&YEAR2.);

ENDRSUBMIT;

Re: Calling a macro using RSubmit

PaigeMiller — Thu, 31 Aug 2023 16:50:28 GMT

The macro must be defined on the remote machine

rsubmit inheritlib=(workdir) remote=worker1;
%macro test1(year);
/* The rest of the macro definition goes here */
%mend;
%TEST1(&YEAR1.)
ENDRSUBMIT;

Re: Calling a macro using RSubmit

Philmingo — Thu, 31 Aug 2023 17:53:51 GMT

That solution would require me to write the same macro within each RSubmit statement for each worker. Is that correct? I am looking for a solution that allows me to write the macro once and call that macro and its variables within each of the RSubmits.

Re: Calling a macro using RSubmit

Tom — Thu, 31 Aug 2023 17:56:09 GMT

Or the macro needs to be smart enough to generate the RSUBMIT/ENDRSUBMIT code itself.

%macro test1(year,remote);
rsubmit &remote;
/* The rest of the macro definition goes here */
endrsubmit;
%mend;

signon worker1 inheritlib=(workdir) ;
signon worker2 inheritlib=(workdir) ;

%test1(&YEAR1.,worker1)
%test1(&year2,worker2)

Re: Calling a macro using RSubmit

PaigeMiller — Thu, 31 Aug 2023 17:57:32 GMT

Looks like @Tom has beaten me to it, but I think he has the answer.

Re: Calling a macro using RSubmit

Tom — Thu, 31 Aug 2023 18:42:43 GMT

@Philmingo wrote:

That solution would require me to write the same macro within each RSubmit statement for each worker. Is that correct? I am looking for a solution that allows me to write the macro once and call that macro and its variables within each of the RSubmits.

Just place the file with the macro definition where each "worker" can see it.

rsubmit worker1;
%include '~/test1.sas';
%test1;
endrsubmit;

If the remote sessions are on other machines then upload the source file and then include it.

rsubmit worker1;
filename mycopy temp;
proc upload infile='~/test1.sas' outfile=mycopy status=n; 
run;
%include mycopy;
%test1;
endrsubmit;

Re: Calling a macro using RSubmit

Philmingo — Thu, 31 Aug 2023 19:43:14 GMT

I tried taking this approach but this has presented new warning and error messages.

%LET YEAR1 = 2021;
%LET YEAR2 = 2022;

%MACRO TEST1(YEAR, WORKER);

libname workdir "%sysfunc(getoption(work))";

rsubmit inheritlib=(workdir) remote=&worker.;

PROC SQL;

CREATE TABLE workdir.CHECKTOTALS_&YEAR. AS SELECT

DATE_OF_SERVICE,

SUM(Script_Cnt) as Scripts,

SUM(Allowed) as Allowed

FROM SOURCE_TABLE

Where claim_status = 'P'

and substr(CMS_PLAN_ID, 1,1) IN('H', 'R', 'S')

and SERVICEMONTH >= &YEAR.01

and SERVICEMONTH <= &YEAR.12

GROUP BY 1

ORDER BY 1

;QUIT;

ENDRSUBMIT;

%MEND TEST1;

SIGNON WORKER1 INHERITLIB = (WORKDIR);

SIGNON WORKER2 INHERITLIB = (WORKDIR);

%TEST1(&YEAR1., worker1);

%TEST1(&YEAR2., worker2);

Warning & Error Messages:

WARNING: Apparent symbolic reference YEAR not resolved.
ERROR 22-322: Syntax error, expecting one of the following: (, AS, LIKE.

ERROR 200-322: The symbol is not recognized and will be ignored.

Re: Calling a macro using RSubmit

Tom — Thu, 31 Aug 2023 20:02:33 GMT

If you want the remote code to reference a macro variable named YEAR you have to create it.

%MACRO TEST1(YEAR, WORKER);
%syslput year=&year /remote=&worker;

rsubmit inheritlib=(work=workdir) remote=&worker.;

PROC SQL;
CREATE TABLE workdir.CHECKTOTALS_&YEAR. AS SELECT 
DATE_OF_SERVICE,
SUM(Script_Cnt) as Scripts,
SUM(Allowed) as Allowed
FROM SOURCE_TABLE
Where claim_status = 'P' 
and substr(CMS_PLAN_ID, 1,1) IN('H', 'R', 'S')
and SERVICEMONTH >= &YEAR.01 
and SERVICEMONTH <= &YEAR.12
GROUP BY 1
ORDER BY 1
;QUIT;
 
ENDRSUBMIT;
 
%MEND TEST1;

Which means the remote session has to exist BEFORE the RSUBMIT statement (ie you cannot use the RSUBMIT statement to create the remote session).

Re: Calling a macro using RSubmit

SASKiwi — Thu, 31 Aug 2023 20:04:35 GMT

That's because macro variables YEAR1 and YEAR2 are only defined in your parent SAS session and not in the RSUBMIT or child session.

Add this statement after signing on to copy your macro variables over to the new session:

%syslput _all_ / remote = &worker;