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!
... View more
