Count years occurrence in aggregated periods

[NewUsrStat]

Hi guys, 

I need your support for a quite complicated task. 

I have the following 

data DB1;
input ID :$20. (Start End)(:date9.);
format Start End date9.;
cards;
0001 01JAN2015 06FEB2017 
0001 08FEB2017 25APR2018 
0001 26APR2018 31DEC2021 
0002 01JAN2017 12JUL2017 
0002 13JUL2017 31DEC2019 
0002 01JAN2020 31OCT2021
;

what I need to do is to add a flag for each year if the ID appears in the *year. Then for each year I need to count one time the flag for each ID if it appears more than one time (but I can do this by myself).

The desired output would be:

data DB1;
input ID :$20. (Start End)(:date9.) F2015 :$20. F2016 :$20. F2017 :$20. F2018 :$20. F2019 :$20. F2020 :$20. F2021 :$20.;
format Start End date9.;
cards;
0001 01JAN2015 06FEB2017 1 1 1 0 0 0 0
0001 08FEB2017 25APR2018 0 0 1 1 0 0 0
0001 26APR2018 31DEC2021 0 0 0 1 1 1 1
0002 01JAN2017 12JUL2017 0 0 1 0 0 0 0
0002 13JUL2017 31DEC2019 0 0 1 1 1 0 0
0002 01JAN2020 31OCT2021 0 0 0 0 0 1 1
;

data want;
input ID :$20.A2015 :$20. A2016 :$20. A2017 :$20. A2018 :$20. A2019 :$20. A2020 :$20. A2021 :$20.;
cards;
0001 1 1 1 1 1 1 1
0002 0 0 1 1 1 1 1
;

My point is that periods are aggregated across years so that year(Start) is ineffective. Can anyone help me please?

[Reeza]

Retain on the array variables and reset it at the start of each ID.

So as you set it to one it stays as one until it's reset.

[ballardw · Accepted solution]

One way:

data temp;
   set db1;
   array a(2015:2021) a2015 - a2021;
   do i=2015 to 2021;
      a[i]= (year(start) le i le year(end));
   end;
   keep id a: ;
run;

proc summary data=temp nway;
   class id;
   var a: ;
   output out=want (drop=_:) max=;
run;

Of course, after you put data, as in a year value, into a variable name it becomes much harder to work with in general.

What will you do with the Want data set.

[NewUsrStat]

Thank you very much! I just need to do summary stats with "want".