BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Philmingo
Fluorite | Level 6

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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).

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
Philmingo
Fluorite | Level 6

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.  

Tom
Super User Tom
Super User

@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;
Tom
Super User Tom
Super User

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)

 

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Philmingo
Fluorite | Level 6

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.
Tom
Super User Tom
Super User

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).

SASKiwi
PROC Star

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;

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1709 views
  • 2 likes
  • 4 in conversation