- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Code below is working fine for the reporting date1 20171231. However I'm not seeing any data if reporting date1 is either 20170331 or 20170630 or 20170930 as %sysfunc in second datastep generates the value of the variable POP as 01, 02, 03,04... with leading zeros.
Second datastep is not working if I remove putn and z2. Any help?
I'm looking for solution in same method only as value of reporting date1 in real life is dynamic and it will be any of the quarter ending date.
%let Reportingdate1=20171231; data have; input POP $; datalines; 1 2 3 4 5 6 7 8 9 10 11 12 ; run; data want; set have; where POP in ("%sysfunc(putn(%substr(&Reportingdate1,5,2),Z2.))" "%sysfunc(putn(%eval(%substr(&Reportingdate1,5,2)-1),Z2.))" "%sysfunc(putn(%eval(%substr(&Reportingdate1,5,2)-2),Z2.))"); run;
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@David_Billa wrote:
@Kurt_Bremser Is there a way to achieve it without touching the macro variable reportingdate1.
How about this?
data want;
set have;
where 0<=%substr(&Reportingdate1,5,2)-input(POP,2.)<=2;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
%let reportingdate1=%sysfunc(inputn(20170331,yymmdd8.));
data want;
set have;
where month(&reportingdate1.) - 2 le input(pop,2.) le month(&reportingdate1.);
run;
Maxim 28: Macro Variables Need No Formats.
If you had dates instead of month numbers, it would be
where qtr(pop) = qtr(&reportingdate1.);
Maxim 33: Intelligent Data Makes for Intelligent Programs.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@Kurt_Bremser Is there a way to achieve it without touching the macro variable reportingdate1. I don't want to format the value of reportingdate1 macro variable as you mentioned.
%let reportingdate1=%sysfunc(inputn(20170331,yymmdd8.));
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
If you want to keep that YYYYMMDD format, then you have to wrap the macro variable into a INPUT function everywhere you use it, just to make it usable with date functions.
The best way to store a date (or time) in a macro variable is the raw, unformatted value.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@David_Billa wrote:
@Kurt_Bremser Is there a way to achieve it without touching the macro variable reportingdate1.
How about this?
data want;
set have;
where 0<=%substr(&Reportingdate1,5,2)-input(POP,2.)<=2;
run;