topic Re: Automatically flagging blocks of 6 months time period in SAS Programming

Automatically flagging blocks of 6 months time period

avbraga — Wed, 27 Jan 2016 19:57:08 GMT

Hi all,

I have the following data set (below), with variables Year, Month and Paid, and I would like to add the column Period.

The column Period should have three values:

0 - for months I'm not interested in,

1 - for the 1st 6 months block period, and

2 - for the 2nd 6 months block period starting at the last Year and Month combination

The reason I am creating this variable is that so I can do a rolling sum for Periods 1 and 2, so I can do percent changes between these two 6 months block time periods.

What I would like to do would be to have SAS look at the LAST Year and Month in the dataset, and flag records from the last date going back 6 months as the 2nd period, and then have SAS flag the 1st period (which would be right before the 2nd period). The variable Period is shown below for visuals.

YEAR MONTH PAID PERIOD
2014 01 $5,484,836.87 0
2014 02 $5,229,032.74 0
2014 03 $5,528,373.34 0
2014 04 $5,855,910.75 0
2014 05 $5,769,180.17 0
2014 06 $5,605,305.96 0
2014 07 $5,800,910.48 0
2014 08 $5,965,676.99 0
2014 09 $6,051,208.88 0
2014 10 $6,959,524.85 0
2014 11 $5,732,182.10 1
2014 12 $6,536,995.80 1
2015 01 $5,458,022.75 1
2015 02 $5,603,222.14 1
2015 03 $6,266,351.95 1
2015 04 $6,216,757.90 1
2015 05 $5,856,030.17 2
2015 06 $6,534,000.39 2
2015 07 $5,780,824.61 2
2015 08 $5,858,441.51 2
2015 09 $5,672,399.61 2
2015 10 $5,857,949.56 2

But I am doing this manually this way:

data paid;

set paid;

period = 0;
/*UPDATE*/
if YEAR = "2015" and MONTH ge "05" then period = 2;
else if YEAR = "2015" and MONTH le "04" then period = 1;
else if YEAR = "2014" and MONTH in ("11","12") then period = 1;
run;

but would love an automated or more elegant solution, so I don't have to manually update these date ranges monthly to define these time periods.

Please let me know your ideas. Thank you!

Re: Automatically flagging blocks of 6 months time period

Steelers_In_DC — Wed, 27 Jan 2016 21:25:42 GMT

Here is one solution, I'm sure there are many:

data want;
set have;
if month in ('01','02','03','04','11','12') then Period = 1;
else period = 2;
run;

Re: Automatically flagging blocks of 6 months time period

avbraga — Thu, 28 Jan 2016 16:24:59 GMT

HI Steelers_In_DC,

thanks for you reply. I should have added that the data set is never static. So the last year and month combination on a sorted dataset could be anything.

So what I am looking for is a more automated solution (maybe with macros?), where sas looks at the last year and month record in the dataset, and back flags two 6 months periods.

Thanks for your help tho!

Re: Automatically flagging blocks of 6 months time period

Steelers_In_DC — Thu, 28 Jan 2016 20:20:47 GMT

I gotcha, I tried to do something a little fancier with a hash but I'm still a beginner with those. Here is a solution based on the end of the file:

data have;
infile cards;
informat YEAR $4. MONTH $2. PAID dollar13.2;
format year $4. month $2. paid dollar13.2;
input YEAR$ MONTH$ PAID;
cards;
2014 01 $5,484,836.87
2014 02 $5,229,032.74
2014 03 $5,528,373.34
2014 04 $5,855,910.75
2014 05 $5,769,180.17
2014 06 $5,605,305.96
2014 07 $5,800,910.48
2014 08 $5,965,676.99
2014 09 $6,051,208.88
2014 10 $6,959,524.85
2014 11 $5,732,182.10
2014 12 $6,536,995.80
2015 01 $5,458,022.75
2015 02 $5,603,222.14
2015 03 $6,266,351.95
2015 04 $6,216,757.90
2015 05 $5,856,030.17
2015 06 $6,534,000.39
2015 07 $5,780,824.61
2015 08 $5,858,441.51
2015 09 $5,672,399.61
2015 10 $5,857,949.56
;run;

data date1;
format date min1 max1 min2 max2 yymmn6.;
set have end=eof;
by year;
date=intnx('month',input(cat(year,month),yymmn6.),0,'e');
if eof then do;
max2=date;
min2=intnx('month',date,-5,'e');
max1=intnx('month',date,-6,'e');
min1=intnx('month',date,-11,'e');
end;
run;

data date2(keep=min1 max1 min2 max2);
set date1 end=eof;
if eof;
run;

proc sql;
create table prep as
select distinct a.date,a.year,a.month,a.paid,b.min1,b.max1,b.min2,b.max2
from date1 a left join
date2 b on
a.date >= b.min1
order by date;

data want;
set prep;
Period = 0;
if min1 <= date <= max1 then Period = 1;
if min2 <= date <= max2 then Period = 2;
drop min: max: date;
run;