SAS Macro to automate the code

[sameer112217]

I want to automate the below code for creating this table

data dummy;
input UW_Year$4. month_diff_modified;
datalines;
2015 3
2015 6
2015 9
2015 12
2015 15
2015 18
2015 21
2015 24
2015 27
2015 30
2015 33
2015 36
2015 39
2015 42
2015 45
2015 48

2015 51

2015 54

2015 57

2015 60
2016 3
2016 6
2016 9
2016 12
2016 15
2016 18
2016 21
2016 24
2016 27
2016 30
2016 33
2016 36

2016 39

2016 42

2016 45
2016 48
2017 6
2017 9
2017 12
2017 15
2017 18
2017 21
2017 24

2017 27

2017 30

2017 33

2017 36
2018 03
2018 06
2018 09
2018 12

2018 15

2018 18

2018 21

2018 24

2019 03

2019 06

2019 09

2019 12

;

run;

The general rule is it should start with April 2015 and the end year should be next year and in my case it is March 2020 since this is the 7th month. IF this is less than 4 months it has to be 2019. The flow should be like this

2015  03

2015 06

.........

........

2015 60  (since 2015 apri to 2020 mar is 60 months) it needs to be increased by 3

2016 03

2016 06

.........

............

2016 48  ( since distance between 2016 apr to 2020 march is 48 months)

Same for 2017, 2018 and 2019

I wrote a macro variable to store previous year. Then I calculated the period between 2015 and 2020 using intck and then i used the do loop and increase by 3 to create the table.. somehow i am stuck in automating it . The area where I am stuck is for 2016 I need to calculate it from 2016 to 2020..

Please let me know of any simpler way  to achieve this task. I need this in the sas dataset table

[sameer112217]

I used just a basic macro to create the table structure however I was stuck in the part where do loop should have calculations for 2016 onward incremental

options symbolgen mlogic mprint;
%macro datecalc;
%global cyear1;
data _null_;
call symputx('cmonth',month(today()));
call symputx('cmonth_name',put(month(today()),monname.));
call symputx('cyear',year(today()));

run;

%put &cmonth. &cyear.;

%if &cmonth >=4 %then %do;
%let cyear1= %sysevalf(&cyear +1);
%end;
%else %do;
%let cyear1=%bquote(&cyear.);
%end;
%put &cyear1.;

data table;

%do i = 2015 %to &cyear1;

%let diff= %sysfunc(intnx('month','01APR&i.,'31MAR&cyear1.'d));

%do j=  3 %to &diff&i by 3;

%end;

%end;

run;

%mend;
%datecalc;

[Kurt_Bremser]

---

@sameer112217 wrote:

I used just a basic macro to create the table structure

options symbolgen mlogic mprint;
%macro datecalc;
%global cyear1;
data _null_;
call symputx('cmonth',month(today()));
call symputx('cmonth_name',put(month(today()),monname.));
call symputx('pmonth',month(intnx('month',today(),-1)));
call symputx('cyear',year(today()));
call symputx('pyear',year(intnx('month',today(),-1)));
run;

%put &cmonth. &cyear.;

%if &cmonth >=4 %then %do;
%let cyear1= %sysevalf(&cyear +1);
%end;
%else %do;
%let cyear1=%bquote(&cyear.);
%end;
%put &cyear1.;
%let diff= %sysfunc(intnx('month','01APR2015'd,'31MAR&cyear1.'d));

/*%do i=2015 %to &cyear1;*/
/*%do f=3 %to */
%mend;
%datecalc;

---

Forget your macro obsession. Learn to use Base SAS first.

[sameer112217]

Wow sir brilliant. Wish I can be as half good as you.

[Kurt_Bremser]

---

@sameer112217 wrote:

Wow sir brilliant. Wish I can be as half good as you.

---

Not brilliant. Just "non-complicated" thinking.

The first thing I noticed: you have a loop over years, so that made up my outer loop.

The second thing I noticed: the final month number is a multiple of 12 and corresponds to the number of years to calculate. Tadaaa: the inner loop!

Strive to see the obvious patterns. Only when you find none, you may have to resort to complicated techniques. But even then, start with the simplest approach and see if you can beat it into shape with some extensions.

[Kurt_Bremser · Accepted solution]

Just two nested do loops:

%let startyear=2015;
%let endyear=2019;

data want;
do year = &startyear. to &endyear.;
  do month_diff_modified = 3 to ((&endyear. + 1) - year) * 12 by 3;
    output;
  end;
end;
run;

proc print data=want noobs;
run;

Result:

        month_diff_
year      modified

2015          3    
2015          6    
2015          9    
2015         12    
2015         15    
2015         18    
2015         21    
2015         24    
2015         27    
2015         30    
2015         33    
2015         36    
2015         39    
2015         42    
2015         45    
2015         48    
2015         51    
2015         54    
2015         57    
2015         60    
2016          3    
2016          6    
2016          9    
2016         12    
2016         15    
2016         18    
2016         21    
2016         24    
2016         27    
2016         30    
2016         33    
2016         36    
2016         39    
2016         42    
2016         45    
2016         48    
2017          3    
2017          6    
2017          9    
2017         12    
2017         15    
2017         18    
2017         21    
2017         24    
2017         27    
2017         30    
2017         33    
2017         36    
2018          3    
2018          6    
2018          9    
2018         12    
2018         15    
2018         18    
2018         21    
2018         24    
2019          3    
2019          6    
2019          9    
2019         12