DATA Step, Macro, Functions and more

Changing dataset from long to wide with calculated variables

Reply
Frequent Contributor
Posts: 132

Changing dataset from long to wide with calculated variables

I have a dataset with patients with at least 2 observations - observed longitudinally. I would like to turn this into an independent dataset as below. What is the most efficient way to do this?

 

 

OLD:

 

ID    YEAR

1        1980

1        1990

2        1975

2        1980

2        1985

 

 

NEW:

 

ID    YEAR     YEARS_UNTIL_2ND  YEARS_UNTIL_LAST      NUM_OBS

1     1980                10                                  10                             2                   

2     1975                 5                                   10                             3

 

PROC Star
Posts: 1,282

Re: Changing dataset from long to wide with calculated variables

[ Edited ]
data have;
input ID    YEAR;
datalines;
1        1980
1        1990
2        1975
2        1980
2        1985
;

data want;
do _n_=1 by 1 until(last.id);
set have;
by id;
if _n_=1 then _t=year;
 else if _n_=2 then YEARS_UNTIL_2ND=year-_t;
if last.id then do;YEARS_UNTIL_LAST =year-_t;NUM_OBS=_n_;year=_t;end;
end;
drop _:;
run;
Frequent Contributor
Posts: 132

Re: Changing dataset from long to wide with calculated variables

Posted in reply to novinosrin

I actually have more variables in my dataset so this is keeping data for my last event, but replacing year with _t.  I think it was meant to work only if we have these specific variables  in the dataset, but how to I maintain the first?

PROC Star
Posts: 1,282

Re: Changing dataset from long to wide with calculated variables

Can you please modify your sample

 

Frequent Contributor
Posts: 132

Re: Changing dataset from long to wide with calculated variables

[ Edited ]
Posted in reply to novinosrin

OLD:

 

ID    YEAR         SYS     DIAS ......................(just few of hundreds of vars)

1        1980        120        90

1        1990        130        85

2        1975        160       100

2        1980        146        98

2        1985        140        88

 

 

NEW:

 

ID    YEAR     YEARS_UNTIL_2ND  YEARS_UNTIL_LAST      NUM_OBS          SYS     DIAS.................(just few of hundreds of vars)

1     1980                10                                  10                             2                 120        90                 

2     1975                 5                                   10                             3                 160       100

PROC Star
Posts: 1,282

Re: Changing dataset from long to wide with calculated variables

see if this works, I am off to class now at my college. If any issues, let me know. I will look into it in 3-4 hours

 


data have;
input ID    YEAR SYS     DIAS;
datalines;
1        1980        120        90
1        1990        130        85
2        1975        160       100
2        1980        146        98
2        1985        140        88
;

data want;
if _n_=1 then do;
  dcl hash H (dataset:'have(obs=0)',ordered: "A") ;
   h.definekey  ("id") ;
   h.definedata (all:'y') ;
   h.definedone () ;
   end;
do _n_=1 by 1 until(last.id);
set have;
by id;
if _n_=1 then do; _t=year;h.add();end;
 else if _n_=2 then YEARS_UNTIL_2ND=year-_t;
if last.id then do;YEARS_UNTIL_LAST =year-_t;NUM_OBS=_n_;year=_t;h.find();end;
end;
drop _:;
run;
Ask a Question
Discussion stats
  • 5 replies
  • 98 views
  • 0 likes
  • 2 in conversation