topic Re: Conditional count (last 5 years observations) in SAS Programming

Conditional count (last 5 years observations)

ohsg94 — Sun, 08 Aug 2021 08:04:25 GMT

Hello everyone,

I'm trying to count a variable called "EI_D" that values 1 for last 5 years.

Here is my dataset.

Year Code EI_D

2010 A 1

2011 A 0

2012 A 1

2013 A 1

2014 A 0

2015 A 1

2016 A 0

2017 A 0

2018 A 1

2014 B 1

2015 B 1

2016 B 0

2017 B 1

2018 B 0

I want my dataset looking like below.

Year Code EI_D Count

2010 A 1

2011 A 0

2012 A 1

2013 A 1

2014 A 0 3

2015 A 1 3 (Since I want to count EI_D that is 1 for last 5 years, I do not include 2010)

2016 A 0 3

2017 A 0 2

2018 A 1 2

2014 B 1

2015 B 1

2016 B 0

2017 B 1

2018 B 0 3

I tried several types of PROC SQL code but they didn't work for me.

I truly appreciate everyone's help...

Re: Conditional count (last 5 years observations)

Patrick — Sun, 08 Aug 2021 08:34:43 GMT

If you search the SAS forums for rolling sum or rolling count you'll find quite a few similar Q&A.

Below one coding option using your sample data.

Depending on your actual data types and if year is just a number or a SAS date value some amendments to the posted code will be required.

data have;
  input Year Code $ EI_D;
  datalines;
2010 A 1
2011 A 0
2012 A 1
2013 A 1
2014 A 0
2015 A 1
2016 A 0
2017 A 0
2018 A 1
2014 B 1
2015 B 1
2016 B 0
2017 B 1
2018 B 0
;

proc sql;
  select distinct
    t1.*,
    case
      when count(*) >= 5 then sum(t2.EI_D)
      else .
      end as sum_ei_d
  from 
    have t1
    left join
    have t2
    on t1.code=t2.code and t2.year between t1.year-4 and t1.year
  group by 
    t1.code, t1.year
  order by
    t1.code, t1.year
  ;
quit;

Re: Conditional count (last 5 years observations)

PaigeMiller — Sun, 08 Aug 2021 10:50:46 GMT

If you have PROC EXPAND, then this is relatively easy, its called a moving sum of the 5 observations. You can use the MOVSUM 5 operator. An example (for moving average) can be found here: https://documentation.sas.com/doc/en/etsug/15.2/etsug_expand_details19.htm#etsug_expand002903 (scroll down)

Re: Conditional count (last 5 years observations)

Kurt_Bremser — Sun, 08 Aug 2021 12:50:17 GMT

Rolling sum in a data step:

data want;
set have;
by code year; /* year to assure correct sort order */
array y {0:4} _temporary_;
if first.code
then do;
  do i = 0 to 4; /* can be omitted, because we only calculate sums after the first 5 years */
    y{i} = 0;
  end;
  i = 1;
end;
else i + 1;
y{mod(_n_,5)} = ei_d;
if i ge 5 then count = sum(of y{*});
drop i;
run;

Untested, posted from my tablet.

Re: Conditional count (last 5 years observations)

Ksharp — Sun, 08 Aug 2021 11:31:56 GMT

data have;
  input Year Code $ EI_D;
  datalines;
2010 A 1
2011 A 0
2012 A 1
2013 A 1
2014 A 0
2015 A 1
2016 A 0
2017 A 0
2018 A 1
2014 B 1
2015 B 1
2016 B 0
2017 B 1
2018 B 0
;

data want;
 if _n_=1 then do;
   if 0 then set have(rename=(EI_D=_EI_D));
   declare hash h(dataset:'have(rename=(EI_D=_EI_D))',hashexp:20);
   h.definekey('year','code');
   h.definedata('_EI_D');
   h.definedone();
 end;
set have;
 _n=0;count=0;
 do _year=year-4 to year;
  if h.find(key:_year,key:code)=0 then do;_n+1;count+_EI_D; end;
 end;
 if _n ne 5 then count=.;
 drop _n _year _EI_D;
run;

Re: Conditional count (last 5 years observations)

PeterClemmensen — Sun, 08 Aug 2021 11:59:31 GMT

@Ksharp Nice!

Re: Conditional count (last 5 years observations)

mkeintz — Sun, 08 Aug 2021 16:38:58 GMT

You want rolling sums of EI_D for "windows" of size 5. If you have exactly one record per year, and no holes, then:LAG is efficient:

data want;
  set have;
  count=sum(lag4(ei_d),lag3(ei_d),lag2(ei_d),lag1(ei_d),ei_d);
  if lag4(code)^=code then count=.;
run;

Re: Conditional count (last 5 years observations)

ohsg94 — Thu, 12 Aug 2021 07:37:55 GMT

I tried it and it worked for me. Thanks a lot.

Re: Conditional count (last 5 years observations)

ohsg94 — Thu, 12 Aug 2021 07:40:27 GMT

This worked for me. Thanks for your help.