Hi community, I am trying to automate a report. When I run the report at the beginning of the month, I want all records from the prior month. The tricky part is billing handles the last day of the monthly differently - all reporting months end on the 30th, except Feb, which end on the 28th. Therefore, I want to set the date range based on the reporting month. Here's what I have so far: data _null_;
*Get prior month (i.e reporting month (rm));
call symputx('rm', PUT(intnx('MONTH',today()-0,-1,'s'), monname3.));
*Get the year of the prior month (i.e reporting month year);
call symputx('rmy', PUT(intnx('MONTH',today()-0,-1,'s'), year4.));
*Get prior 2 month (i.e reporting month (rm2));
call symputx('rm2', PUT(intnx('MONTH',today()-0,-2,'s'), monname3.));
*Get the year of the prior 2 month (i.e reporting month year 2);
call symputx('rmy2', PUT(intnx('MONTH',today()-0,-2,'s'), year4.));
run;
*Test macros. If I ran in Feb 2021, I'd get:;
%put &rm; *Jan;
%put &rmy; *2021;
%put &rm2; *Dec;
%put &rmy2; *2020;
*Now I want to create code that does the following:
*If reporting month is Jan, then keep only records where Dec 31, 2020 <= Date <= Jan 30, 2021;
*If reporting month is Feb, then keep only records where Jan 31, 2020 <= Date <= Feb 28, 2021;
*etc. for the rest of the months;
*Here's how I'm able to make the timestamp field dynamic, but I can't figure out how apply
specific ranges based on the reporting month;
*Example if I want Jan report, I want range to be 31Dec2020 to 30Jan2021;
%let yyyy=&rmy;
%let mm=&rm;
%let xxxx=&rmy2;
%let nn=&rm2;
data Test&mm.&yyyy.;
set data;
where "30&nn&xxxx:0:0:0"dt <= Date <= "30&mm&yyyy:0:0:0"dt;
run; Does anyone know how to do this? Should I change my approach - Is there a better way to fulfill this reporting requirement? A
... View more
