Solved
New Contributor
Posts: 4

# Counting spellnr and durations

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!

Accepted Solutions
Solution
‎05-25-2016 05:43 AM
Super User
Posts: 10,215

## Re: Counting spellnr and durations

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Solution
‎05-25-2016 05:43 AM
Super User
Posts: 10,215

## Re: Counting spellnr and durations

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 4

## Re: Counting spellnr and durations

Thanks a lot, Kurt! It totally worked!

🔒 This topic is solved and locked.

Discussion stats
• 2 replies
• 253 views
• 1 like
• 2 in conversation