Counting spellnr and durations

[NielsKraaer]

Hi!

I have data like the following:

ID           y_9132           y_9133       ....       y_0001 .....        y_1604

10302     621                 621            ....       998                    998

10303     111                 112            ...        111                    111

Such that, individual 10302 has the status 621 for some time, and then he makes a transtion into another state. The label y_yyww stands for the year and weeknr, such that y_9132 means weeknr 32 in the year 1991.

I want the data:

ID         spellnr    duration              exit
10302       1            1                  0
10302       1            2                  0
10302       1            3                  0
...
10302       1            15                1
10302       2            1                  0
....
10303       1            1                  0
... osv

Such that the individual is asked each week what state he is in up until he makes a transition. Then I want to count the next spell as spellnr 2 and so on for all individuals.

Thanks!

[Kurt_Bremser · Accepted solution]

First, transpose your dataset from wide to long format. Then you can use proc sort and by processing to get your result:

data have;
input ID y_9132 y_9133 y_9134 y_9135;
cards;
10302 621 621 998 998
10303 111 112 111 111
;
run;

proc transpose
  data=have
  out=have_transposed (rename=(_NAME_=yearweek COL1= status))
;
by ID;
run;

data have_transposed;
set have_transposed;
if substr(yearweek,3,1) > '2'
then substr(yearweek,1,2) = '19';
else substr(yearweek,1,2) = '20';
run;

proc sort data=have_transposed;
by ID yearweek;
run;

data want (keep=ID spellnr duration exit);
set have_transposed;
by ID status notsorted;
retain spellnr duration old_status exit;
if first.ID
then do;
  spellnr = 1;
  duration = 0;
end;
duration + 1;
if last.status
then do;
  exit = 1;
  output;
  duration = 0;
  spellnr + 1;
end;
else do;
  exit = 0;
  output;
end;
run;

[NielsKraaer]

Thanks a lot, Kurt! It totally worked!