create a new variable via calculating values by group

Lily07 — Thu, 06 Jun 2019 16:28:56 GMT

Hello

I want to make a new variable from panel data, which is strongly unbalanced, but I don't how to calculate within a group.

Here is an example of data.

From this kind of table

id	year	death
1	2000	0
1	2002	0
1	2003	1
2	2000	0
2	2001	0
2	2002	0
3	2000	0
3	2002	0
3	2003	0
4	2001	0
4	2002	0
4	2003	1
5	2001	0
5	2003	0

I want to calculate survival time in years. Something like this,

id	time	death	studytime
1	1	1	4
1	3	1	4
1	4	1	4
2	1	0	3
2	2	0	3
2	3	0	3
3	1	0	4
3	3	0	4
3	4	0	4
4	2	1	3
4	3	1	3
4	4	1	3
5	2	0	3
5	4	0	3

How can I do this?

Please help me.

Thanks so much in advance.

Re: create a new variable via calculating values by group

Tom — Thu, 06 Jun 2019 17:33:00 GMT

You can take advantage of the fact that PROC SQL will automatically remerge summary statistics back with the detailed records to do this with a simple SQL query.

data have ;
  input id year death;
cards;
1 2000 0
1 2002 0
1 2003 1
2 2000 0
2 2001 0
2 2002 0
3 2000 0
3 2002 0
3 2003 0
4 2001 0
4 2002 0
4 2003 1
5 2001 0
5 2003 0
;

proc sql ;
create table want as 
  select id
       , year-min(year)+1 as time
       , max(death) as death
       , max(year)-min(year)+1 as studytime
  from have
  group by id
  order by id, calculated time
;
quit;

Results

Obs    id    time    death    studytime

  1     1      1       1          4
  2     1      3       1          4
  3     1      4       1          4
  4     2      1       0          3
  5     2      2       0          3
  6     2      3       0          3
  7     3      1       0          4
  8     3      3       0          4
  9     3      4       0          4
 10     4      1       1          3
 11     4      2       1          3
 12     4      3       1          3
 13     5      1       0          3
 14     5      3       0          3

topic Re: create a new variable via calculating values by group in SAS Data Management

create a new variable via calculating values by group

Re: create a new variable via calculating values by group