topic Re: How filter last month and previous month data from date column in SAS Programming

How filter last month and previous month data from date column

Badshaah — Tue, 13 Dec 2016 15:52:13 GMT

We have single data table in which we only required last month data ,
So i can calculate last date by max date but ..need logic for second last month.

Re: How filter last month and previous month data from date column

ballardw — Wed, 14 Dec 2016 18:09:52 GMT

First question: are your dates actually SAS date or datetime valued variables, character or something else? The functions to manipulate dates require SAS date, time or datetime values and to know which you have.

Re: How filter last month and previous month data from date column

Badshaah — Tue, 13 Dec 2016 15:58:09 GMT

It's form base table having variable in date9 format

Re: How filter last month and previous month data from date column

Jagadishkatam — Tue, 13 Dec 2016 16:01:04 GMT

Could you please provide sample data with expected output to help you better.

Re: How filter last month and previous month data from date column

Badshaah — Tue, 13 Dec 2016 16:14:07 GMT

employee_
Obs id jobcat transmonth wage rating

1 1254 one 29AUG1998 $11000.00 2
2 9936 two 17OCT1998 $5,000.00 0
3 7529 two 04NOV1998 $9,000.00 1
4 9154 one 25NOV1998 $10000.00 2
5 7741 two 18NOV1998 $9,500.00 3
6 8896 two 11OCT1998 $9,600.00 4
7 6658 one 01MAY1999 $12000.00 4
8 7854 two 27OCT1998 $9,600.00 3
9 9458 two 13NOV1998 $8,999.00 1
10 7887 two 19OCT1998 $9,050.00 0

Re: How filter last month and previous month data from date column

Badshaah — Tue, 13 Dec 2016 16:16:52 GMT

We have two calculate only last month which max of month and .. Second last month

Re: How filter last month and previous month data from date column

Astounding — Tue, 13 Dec 2016 16:45:43 GMT

So in the sample data you provided, 25NOV1998 is the maximum value for the incoming dates. What would you like the beginning and ending points to be for your time period?

Re: How filter last month and previous month data from date column

Badshaah — Tue, 13 Dec 2016 16:48:31 GMT

It's just sample data .. If 28nov2016 is max then max -1 months 30oct2016 is also needed but we have to convert date9. To monyy7. Format which only extract month and year.

Re: How filter last month and previous month data from date column

Astounding — Tue, 13 Dec 2016 16:51:43 GMT

OK. Assuming you have the max date already calculated:

cutoff_date = intnx('month', max_date, -1, 'e');

This gets you a SAS date value that you can express in any format that you choose.

Re: How filter last month and previous month data from date column

SuryaKiran — Tue, 13 Dec 2016 18:25:46 GMT

data one ;

infile datalines;

retain obs id jobcat transmonth wage rating;

format transmonth date9. wage dollar10.2 ;

input obs id jobcat $ transmonth date9. wage dollar10.2 rating;

datalines;

1 1254 one 29AUG1998 $11000.00 2

2 9936 two 17OCT1998 $5,000.00 0

3 7529 two 04NOV1998 $9,000.00 1

4 9154 one 25NOV1998 $10000.00 2

5 7741 two 18NOV1998 $9,500.00 3

6 8896 two 11OCT1998 $9,600.00 4

7 6658 one 01MAY1998 $12000.00 4

8 7854 two 27OCT1998 $9,600.00 3

9 9458 two 13NOV1998 $8,999.00 1

10 7887 two 19OCT1998 $9,050.00 0

;

run;

/* -Finding the max date and storing in a macro*/

proc sql noprint;

select max(transmonth) INTO : maxdate

from one;

quit;

%put &maxdate;

run;

/* -Getting all the records with months as max date and month prior */

data two;

retain obs id jobcat transmonth wage rating;

FORMAT transmonth MONYY7.;

set one;

if month(transmonth)= month(&maxdate) OR month(transmonth)= month(&maxdate)-1;

run;

Re: How filter last month and previous month data from date column

Badshaah — Wed, 14 Dec 2016 09:39:09 GMT

Thanks suryakiran & Astounding.

Re: How filter last month and previous month data from date column

Astounding — Wed, 14 Dec 2016 11:50:51 GMT

Just be careful to adjust the solution for January. As is, the program will look for month=0 instead of month=12.

Re: How filter last month and previous month data from date column

Badshaah — Wed, 14 Dec 2016 12:03:42 GMT

Then what's the solution for that

Re: How filter last month and previous month data from date column

Astounding — Wed, 14 Dec 2016 12:10:12 GMT

Based on the tools that I had posted originally:

if ( intnx('month', max_date, -1, 'b') <= transmonth <= intnx('month', max_date, -1, 'e') );

In fact, the solution as posted only looks at month. If you have a data set that includes more than one year of data, you can be selecting based on month only, including data from a previous year.

Re: How filter last month and previous month data from date column

Badshaah — Wed, 14 Dec 2016 12:11:08 GMT

Grand salute ..to u .. Thanks

Re: How filter last month and previous month data from date column

Badshaah — Wed, 14 Dec 2016 12:12:22 GMT

R u pull out this one
a requirement of multiple file have different locations are export to excel ..each file to each sheet ... Mainly we want on proc report if possible.