topic Re: Using mdy function to Extract the date and filter 12 months older data in SAS Programming

Using mdy function to Extract the date and filter 12 months older data

JJP1 — Fri, 22 Nov 2019 15:27:31 GMT

Hi. I have a dataset contains year, month columns but I don't have day columns. So using this columns I need to write a condition to filter only 12months old records from today Please help. Year is holding as 2019 and month is 10

Re: Using mdy function to Extract the date and filter 12 months older data

PaigeMiller — Fri, 22 Nov 2019 15:32:32 GMT

Set the day to be 1 in the MDY function.

Re: Using mdy function to Extract the date and filter 12 months older data

unison — Fri, 22 Nov 2019 15:45:02 GMT

Here's an example. This dataset contains dates in the 1990's so I set today to 12/31/1994 but you can use:

%let tdy = %sysfunc(today(),date9.);
%put =&tdy;

Example:

%let tdy = '31DEC1994'd;

data have;
set sashelp.prdsale;
format date date9.; 
month_num = month(month);
date = mdy(month_num,1,year);
run;

data want;
set have;
where date between intnx("month",&tdy.,-11,"b") and &tdy.;
run;

proc freq data=want; 
tables month*year /nocol nocum nopercent norow; 
run;

-unison

Re: Using mdy function to Extract the date and filter 12 months older data

Astounding — Fri, 22 Nov 2019 18:07:24 GMT

You have to do the hard part ... define the problem better.

Today is November 22, 2019. Using only month and year, what month/year combinations would be included in your 12 month period?

Once the rules are established, the programming would be relatively easy for many of the people who post here.

Re: Using mdy function to Extract the date and filter 12 months older data

JJP1 — Sat, 23 Nov 2019 04:17:18 GMT

Why at the end proc freq you are using please

Re: Using mdy function to Extract the date and filter 12 months older data

unison — Sat, 23 Nov 2019 04:58:32 GMT

Just as a check to make sure the correct observations are pulled.

-unison

Re: Using mdy function to Extract the date and filter 12 months older data

JJP1 — Sat, 23 Nov 2019 05:12:18 GMT

Sorry for confusion.
I have below sample data
Number year month
19 2019 10
20 2019 10
30 2018. 5

I want to keep the above data only for 12 months older .i can do this only
using year and month columns.
so sample output should exclude 2018 record as it is not 12 months older .
I know I can use mdy function.but Iam not sure how to apply the function.
Please help

Re: Using mdy function to Extract the date and filter 12 months older data

JJP1 — Sat, 23 Nov 2019 06:50:18 GMT

Thanks @Paige. Would you please help on complete code please

Re: Using mdy function to Extract the date and filter 12 months older data

PaigeMiller — Sat, 23 Nov 2019 11:36:16 GMT

@JJP1 wrote:
Thanks @Paige. Would you please help on complete code please

What is wrong with the code from @unison ?

Re: Using mdy function to Extract the date and filter 12 months older data

Astounding — Sat, 23 Nov 2019 14:44:05 GMT

The key issue is still unaddressed. Let me spell it out in a more specific way.

The following represent 3 dates on which the program might run:

November 30, 2019

December 1, 2019

December 15, 2019

For each date, specify the first year/month and the last year/month you would like to include.

Re: Using mdy function to Extract the date and filter 12 months older data

Pmyosh — Mon, 25 Nov 2019 03:43:14 GMT

Hi JJP1,

Do you mean all your data looks like this:

Year Month

2019 01

2018 02

2019 03

But you dont have a day Variable? Is your intention to get the full 12 months regardless of the date?

E.g., today is Nov 25, 2019, you want to include From Dec 01, 2018 to today?

Since you don't have day variable, you can default to 01 all the days.

Then read as yyyymmdd, then use intck to count 12 months from today

data ck ;
year=2019 ;
month=9 ;
new_month = COMPRESS(PUT(year,4.)||PUT(month,z2.)||"01") ;

FORMAT inputasdate date9. ;
inputasdate = INPUT(new_month,yymmdd8.) ;
_12months = intck("months",inputasdate, today()) ;

IF 0<= _12months <=12 ;
RUN ;

Please take note that the year and month, I suppose they are numbers

Please let us know if we get your issue correctly?