CODEWITHSUNDEEP
sassas-macro
gbarbe77
gbarbe77

Reputation: 15

Macro Do loop not working properly in RSUBMIT

So I have this code that works well for one year, but I need to convert it as a loop so it works for years from 1970 to 2015.

Here is the code for 1 year that I specify in a %let statement.

%let year=1970
rsubmit; 
data home.historical_returns_&year;
set home.crspdata;
where (year <= &year - 1) and (year >= &year - 5);
returns_count + 1;
by id year;
if first.id or missing(tot_ret) then returns_count = 1;
run;
endrsubmit;

So far, that code works great for me. Now, I am trying to use a loop so I do it for year 1970 to 2015.

I have came up with this. Which looks like it works great, but the year stays at 1970.

%macro GMV;
rsubmit;
%do year=1970 %to 2015;
data home.historical_returns_&year;
set home.crspdata;
where (year <= &year - 1) and (year >= &year - 5);
returns_count + 1;
by id year;
if first.id or missing(tot_ret) then returns_count = 1;
run;
%end;
endrsubmit;

%mend GMV;

%GMV

In the log, I see that the &year in the name never actually changes from 1970 to 1971 to 1972 and so on. So I do not end up with the 45 different datasets that I need.

Anybody ever had this problem?

Thank you!

Upvotes: 1

Views: 732

Answers (2)

Joe
Joe

Reputation: 63424

You're mixing up remote processing with local processing in a way that's going to cause problems like this. Your macro variable won't be updated (and I'm a bit surprised it's not throwing an error about the %do loop, personally).

rsubmit;

%macro GMV;
%do year=1970 %to 2015;
data home.historical_returns_&year;
set home.crspdata;
where (year <= &year - 1) and (year >= &year - 5);
returns_count + 1;
by id year;
if first.id or missing(tot_ret) then returns_count = 1;
run;
%end;

%mend GMV;

%GMV
endrsubmit;

Put the whole macro in the rsubmit to get the result you're looking for - or put the whole rsubmit in the macro (not as good of an idea in my opinion, though Tom in comments notes that it might be the safer option in some cases).

Upvotes: 1

Tom
Tom

Reputation: 51566

If you want to reference a macro variable in the code that you RSUBMIT then the macro variable needs to exist in the remote session.

%macro GMV(start,end);
   %local year;
   %do year=&start %to &end;
      %syslput year=&year;
      rsubmit;
       data home.historical_returns_&year;
         set home.crspdata;
         by id year;
         where (year <= &year - 1) and (year >= &year - 5);
         returns_count + 1;
         if first.id or missing(tot_ret) then returns_count = 1;
       run;
     endrsubmit;
   %end;
 %mend GMV;

 %GMV(1970,2015);

Upvotes: 1

Related Questions