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.
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.