DATA Step, Macro, Functions and more

Counting spellnr and durations

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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: 6,972

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

View solution in original post


All Replies
Solution
‎05-25-2016 05:43 AM
Super User
Posts: 6,972

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
New Contributor
Posts: 4

Re: Counting spellnr and durations

Thanks a lot, Kurt! It totally worked!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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