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: 7,758

Re: Counting spellnr and durations

Posted in reply to NielsKraaer

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: 7,758

Re: Counting spellnr and durations

Posted in reply to NielsKraaer

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

Posted in reply to KurtBremser

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
  • 212 views
  • 1 like
  • 2 in conversation