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?
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.