Query to filter data on reporting quarter

[David_Billa]

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;

[Kurt_Bremser]

%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.

[David_Billa]

@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.));

[Kurt_Bremser]

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. 

[FreelanceReinh · Accepted solution]

---

@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;