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!
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;
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;
Thanks a lot, Kurt! It totally worked!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.