Filter on quarter months value from quarter ending date

[David_Billa]

I've a data as follows. Now I want to filter the data based on the value of macro variable. If I've a macro variable value as

%let REPORTINGDATE =20170930; then I want to filter the data of year value (e.g. 2017) of the macro variable and the quarter months values (e.g. 7,8,9) from the macro variable

ID	Purchase_Year	Purchase_Month
1	2017	                9
1	2017	                7
1	2018	                11
1	2018	                12

I've used the code as below but it's not working as excepted. Instead of filtering the first two records from the data, it is filtering only the first record.

Proc SQL;
Create table test as
select * from have 
where Purchase_Year=%substr(&Reportingdate1,1,4) & Purchase_Month in (%substr(&Reportingdate1,5,2), %substr(&Reportingdate1,5,2)-1,%substr(&Reportingdate1,5,2)-2)

Desired Result:

ID	Purchase_Year	Purchase_Month
1	2017	                9
1	2017	                7

Macro variable value will always be quarter ending date. So I want to filter Purchase_Month values for (1,2,3) or (4,5,6) or (7,8,9) or (10,11,12). Also I'm looking for result using where clause. Any help?

[hhinohar · Accepted solution]

Hello.
I made a modification to your program.
Extracting year and month from your macro variables can be done by calling %sysfunc function.

%sysfunc function
https://documentation.sas.com/?cdcId=vdmmlcdc&cdcVersion=8.1&docsetId=mcrolref&docsetTarget=p1o13d7w...

For quarter calculation , macro variable is a character string so arithmetic calculation is not possible.
Use %eval function for this purpose.

%eval function
https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=mcrolref&docsetTarget=n07...

data have;
infile datalines dlm="09"x;
input ID Purchase_Year Purchase_Month;
datalines;
1	2017	9
1	2017	7
1	2018	11
1	2018	12
;
run;

*macro variable;
%let REPORTINGDATE=20170930;
*Use datastep function by calling %sysfunc function;
*year;
%let y=%sysfunc(year(%sysfunc(inputn(&REPORTINGDATE,anydtdte.))));
*month;
%let m=%sysfunc(month(%sysfunc(inputn(&REPORTINGDATE,anydtdte.))));

*SAS macro string is a set of character so doesn't do arithmetic calculation by it self.
Use %eval function for this purpose.;
options mprint;
Proc SQL;
Create table test as
  select * from have 
    where 
      Purchase_Year=&y and Purchase_Month in (&m, %eval(&m-1),%eval(&m-2));
quit;

[Kurt_Bremser]

First, make your data intelligent. Date-related values should be stored as SAS date values.

data good;
set have;
p_month = mdy(purchase_month,1,purchase_year);
format p_month yymmd7.;
run;

Then you can apply functions to filter:

%let reportingdate=%sysfunc(inputn(20170930,yymmdd8.));
/* no format necessary, see Maxim 28 */

data want;
set good;
where intnx('qtr',&reportingdate.,0,'b') le p_month le intnx('qtr',&reportingdate.,0,'e');
run;