topic Re: Changing dataset from long to wide with calculated variables in SAS Programming

Changing dataset from long to wide with calculated variables

Melk — Wed, 11 Apr 2018 00:23:13 GMT

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

Re: Changing dataset from long to wide with calculated variables

novinosrin — Wed, 11 Apr 2018 00:40:33 GMT

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;

Re: Changing dataset from long to wide with calculated variables

Melk — Wed, 11 Apr 2018 22:13:37 GMT

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?

Re: Changing dataset from long to wide with calculated variables

novinosrin — Wed, 11 Apr 2018 22:14:48 GMT

Can you please modify your sample

Re: Changing dataset from long to wide with calculated variables

Melk — Wed, 11 Apr 2018 22:18:05 GMT

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

Re: Changing dataset from long to wide with calculated variables

novinosrin — Wed, 11 Apr 2018 22:24:59 GMT

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;