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