topic Re: How to filter observation from month to another month? in SAS Programming

How to filter observation from month to another month?

Tri_Luong — Wed, 31 Oct 2018 04:03:46 GMT

Dear experts,

I am trying to filter observation for latest 3 months with the condition in variable "Report_Month", I did manage to filter for currently month (October) with the code:

Where REPORT_MONTH = PUT(DATE(),MMYYS10.);

Would you mind instructing us, how can I filter the observations from a period (let say AUG/2018 to OCT/2018) ?

Notes: the value format of the variable looks like "08/2018", "09/2018","10/2018", etc.

Thank you for your helping.

Re: How to filter observation from month to another month?

kiranv_ — Wed, 31 Oct 2018 09:34:18 GMT

i will use month and intnx functions to solve this. something like below

data have;
date = '09Oct2018'D;
output;
date = '09Oct2018'D;
output;
date = '09SEP2018'D;
output;
date = '09AUG2018'D;
output;
date = '05AUG2018'D;
output;
date = '09JUN2018'D;
output;
format date date9.;; 
run;

data want;
set have;
where month(date) le month(today())
and month(date) ge month(intnx('month', today(), -2)) ;
run;

Re: How to filter observation from month to another month?

gamotte — Wed, 31 Oct 2018 09:41:20 GMT

Hello,

If you want to manipulate date, you should first convert your string dates into date format.

data have;
input strdate $;
format date mmddyy10.;
date=input(strdate,anydtdte.);
put date;
cards;
01/2018
02/2018
03/2018
04/2018
05/2018
06/2018
07/2018
08/2018
09/2018
10/2018
;
run;

proc sql;
SELECT * FROM have
WHERE date BETWEEN '01Aug2018'd AND '01Sep2018'd;
quit;

Re: How to filter observation from month to another month?

Jagadishkatam — Wed, 31 Oct 2018 10:29:52 GMT

Alternatively,

data want;
set have;
where input(substr(put(date,date9.),3),monyy7.) <= input(substr(put(today(),date9.),3),monyy7.) <= input(substr(put(date,date9.),3),monyy7.) ;
run;