topic Re: Dynamic month filter in data in SAS Programming

Dynamic month filter in data

harshpatel — Mon, 24 Aug 2020 14:22:51 GMT

Hi,

I want to filter data based on month and year selection

IF month is January and year is 2020 then data should filter as January2020 and December2019

But if month is not in January and year is 2020 then data should filter as February2020 and January2020 only two Months

I want this in SAS coding

Basically I want to compare two months only but for January scenario bid different as explained above
Kindly help me on this
Thanks,
Harsh

Re: Dynamic month filter in data

Reeza — Mon, 24 Aug 2020 15:09:53 GMT

Is it only those two months or does it need to be dynamic for other situations?
Will you ever need March, April? Is the issue here crossing the year boundary where you want the most recent 2 months or something like that?

Either way, you can increment dates easily using INTNX().

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354

@harshpatel wrote:
Hi,

I want to filter data based on month and year selection

IF month is January and year is 2020 then data should filter as January2020 and December2019

But if month is not in January and year is 2020 then data should filter as February2020 and January2020 only two Months

I want this in SAS coding

Basically I want to compare two months only but for January scenario bid different as explained above
Kindly help me on this
Thanks,
Harsh

Re: Dynamic month filter in data

harshpatel — Mon, 24 Aug 2020 15:36:04 GMT

Hi,
I will pass macro
% let month= January
% let year= 2020
If I will pass these macros then data should be filter as January 2020 and December 2019
But if I will change macro
Month= February
Year= 2020
Then data should be filter as February 2020 and January 2020

Re: Dynamic month filter in data

Reeza — Mon, 24 Aug 2020 15:40:05 GMT

What if I pass month=April and year=2020?

Re: Dynamic month filter in data

harshpatel — Mon, 24 Aug 2020 15:42:15 GMT

Then Data should be filter as
April 2020 and March 2020

Re: Dynamic month filter in data

Reeza — Mon, 24 Aug 2020 16:17:15 GMT

So your criteria is month specified and previous month. This works but it'll depend exactly on how you're trying to use the parameters. This is a fully worked example that you can run but you'll need to apply the idea to your own code.

*input parameters;
%let month = January;
%let year = 2004;


*create macro variables for next step;
data _null_;
*convert to SAS date;
month_select = input(catt('01', substr("&month", 1, 3), &year), date9.);
*previous month;
month_prev = intnx('month', month_select, -1, 'b');

*create macro variables;
call symputx('month_select', month_select);
call symputx('month_prev', month_prev);

run;

*check macro variables;
%put %sysfunc(putn(&month_select, date9)) ;
%put %sysfunc(putn(&month_prev, date9)) ;


*filter data;
data stocks;
set sashelp.stocks;
where intnx('month', date, 0, 'b') in (&month_select., &month_prev.);
run;

Note the usage of INTNX() to align the dates to the beginning of hte month for the comparison to work. This is important as 01Jan2019 is not the same as 03Jan2019 and SAS dates require a day component. Setting everything to the first of the month negates this issue.

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354

@harshpatel wrote:
Then Data should be filter as
April 2020 and March 2020

Re: Dynamic month filter in data

harshpatel — Tue, 25 Aug 2020 04:16:43 GMT

Thanks Reeza,
Its Working for me