topic Re: Query to filter data on reporting quarter in SAS Programming

Query to filter data on reporting quarter

David_Billa — Mon, 01 Mar 2021 09:55:41 GMT

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;

Re: Query to filter data on reporting quarter

Kurt_Bremser — Mon, 01 Mar 2021 10:20:26 GMT

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

Re: Query to filter data on reporting quarter

David_Billa — Mon, 01 Mar 2021 11:39:55 GMT

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

Re: Query to filter data on reporting quarter

Kurt_Bremser — Mon, 01 Mar 2021 12:04:11 GMT

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.

Re: Query to filter data on reporting quarter

FreelanceReinh — Mon, 01 Mar 2021 12:43:47 GMT

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