Use %synfunc to create variables

[Solph]

I've different years of data, with file names in fiscal year format, e.g. 1819 for 2018 data, 1718 for 2017 data, etc. And I need to retrieve the data going back to previous 5 years. Here the code I tried to write but it didn't work. 

- the last year of data is 2018, thus %let LastYrQ4 = 1819Q2 (Q2 as for the 2nd Quarter)

- Use %i = 1 %to 5 to get data going from 2018 to 2014 

So Yr&i is to generate values 2018, 2017, 2016, 2015 and 2014

and  UrT&i is to generate values 1819, 1718, 1617, 1516, 1415

Not sure why this code didn't work. Any tips? Thanks.

%let LastYrQr = 1819Q2;
%let Yr = %sysfunc(SUBSTR(&LastYrQr,3,2));
%macro getFYs();
     %do i = 1 %to 5;
           %let Yr&i =  20%sysfunc(putn(%eval(&Yr-&i),z2.));  
         %let YrT&i = %sysfunc(putn(%eval(&Yr-&i),z2.))%sysfunc(putn(%eval(&Yr+1-&i),z2.));
     %end;
%mend;
%getFYs();

[Solph]

I've different years of data, with file names in fiscal year format, e.g. 1819 for 2018 data, 1718 for 2017 data, etc. And I need to retrieve the data going back to previous 5 years. Here the code I tried to write but it didn't work. 

- the last year of data is 2018, thus %let LastYrQ4 = 1819Q2 (Q2 as for the 2nd Quarter data)

- Use %i = 1 %to 5 to get data going from 2018 to 2014 

So Yr&i is to generate values 2018, 2017, 2016, 2015 and 2014

and  UrT&i is to generate values 1819, 1718, 1617, 1516, 1415

Not sure why this code didn't work. Any tips? Thanks.

%let LastYrQr = 1819Q2;
%let Yr = %sysfunc(SUBSTR(&LastYrQr,3,2));
%macro getFYs();
     %do i = 1 %to 5;
           %let Yr&i =  20%sysfunc(putn(%eval(&Yr-&i),z2.));  
         %let YrT&i = %sysfunc(putn(%eval(&Yr-&i),z2.))%sysfunc(putn(%eval(&Yr+1-&i),z2.));
     %end;
%mend;
%getFYs();

[novinosrin]

Duplicate thread????

[Solph]

Thanks. Looks like a glitch. Can't find Delete button. Let me see if it can be done.

[novinosrin]

No worries. I have merged both the threads into one. Have a good one!

[novinosrin · Accepted solution]

Globalise your local macro vars with  %global  Yr&i YrT&i;

So the tweak-->

%let LastYrQr = 1819Q2;
%let Yr = %sysfunc(SUBSTR(&LastYrQr,3,2));
%macro getFYs();
     %do i = 1 %to 5;
     %global  Yr&i YrT&i;
           %let Yr&i =  20%sysfunc(putn(%eval(&Yr-&i),z2.));  
         %let YrT&i = %sysfunc(putn(%eval(&Yr-&i),z2.))%sysfunc(putn(%eval(&Yr+1-&i),z2.));
     %end;
%mend;
%getFYs();

 To your-"Not sure why this code didn't work. Any tips? Thanks."

Your code works and does create those macro vars as intended. However, they remain only during the execution of the macro definition %getFYs as local macro variables. So once the execution is complete the local macro vars cease to exist or in other words cannot be called in open code. Therefore, globalising those local vars overrides the default behavior. 

Now you can test your creation with %put &yrT5 &yr5;

[Solph]

Thanks much for the tips. Really appreciated it.

[Tom]

Just make the variables global and they will exist after the macro stops.

You can make your code simpler.

First there already is a macro function for SUBSTR() so no need to use %SYSFUNC() to call the SAS function substr() instead.

Also %SYSFUNC() will perform arithmetic on the arguments so no need for extra %EVAL() calls.

You can get the last two digits of a number like 2018 by using the MOD() function to find the remainder on division by 100.

Also why not  make a macro that take a YEAR value as input?

%macro getFYs(yr);
  %local i ;
  %do i = 1 %to 5;
    %global yr&i yrT&i ;
    %let Yr&i =  %eval(&yr-&i);
    %let YrT&i = %sysfunc(mod(&yr-&i,100),z2)%sysfunc(mod(&Yr+1-&i,100),z2);
  %end;
%mend;

%let LastYrQr = 1819Q2;
%getFYs(20%substr(&lastyrqr,3,2));

Results:

340  %getFYs(20%substr(&lastyrqr,3,2));
341  %put &=Yr1 &=YrT1 &=Yr5 &=YrT5 ;
YR1=2018 YRT1=1819 YR5=2014 YRT5=1415
342
343
344  %getFYs(2002);
345  %put &=Yr1 &=YrT1 &=Yr5 &=YrT5 ;
YR1=2001 YRT1=0102 YR5=1997 YRT5=9798