I have a list of observations and for each observation, I have a several dates, so that it looks something like this:
Obs | Dates |
---|---|
1 | 1/20/2004 |
2 | 3/25/2004,2/13/2006,3/30/2006,2/13/2006 |
3 | . |
4 | 7/15/2004,10/01/2004,4/20/2004 |
5 | 6/1/2004,1/20/2006,6/1/2007,8/1/2007,3/1/2009,5/13/2004 |
A single observation could have one date repeated. Essentially I want to go from the above table to something like this:
Obs | Dates |
---|---|
1 | 1/20/2004 |
2 | 3/25/2004 |
2 | 2/13/2006 |
2 | 3/30/2006 |
3 | . |
and so on....
The above now has multiple records for a single patient, based on the number of UNIQUE dates from the first table.
How can I go from the first table to the second in SAS? I'm very grateful for your help.
OK.
data have; infile cards expandtabs dlm=' '; input obs dates : $200. ; cards; 1 1/20/2004 2 3/25/2004,2/13/2006,3/30/2006,2/13/2006 3 . 4 7/15/2004,10/01/2004,4/20/2004 5 6/1/2004,1/20/2006,6/1/2007,8/1/2007,3/1/2009,5/13/2004 ; run; data temp; set have; do i=1 to countw(dates,','); date=scan(dates,i,','); output; end; keep obs date; run; proc sort data=temp out=want nodupkey; by obs date; run;
Ksharp
OK.
data have; infile cards expandtabs dlm=' '; input obs dates : $200. ; cards; 1 1/20/2004 2 3/25/2004,2/13/2006,3/30/2006,2/13/2006 3 . 4 7/15/2004,10/01/2004,4/20/2004 5 6/1/2004,1/20/2006,6/1/2007,8/1/2007,3/1/2009,5/13/2004 ; run; data temp; set have; do i=1 to countw(dates,','); date=scan(dates,i,','); output; end; keep obs date; run; proc sort data=temp out=want nodupkey; by obs date; run;
Ksharp
Thanks so much!
As a followup, how can I create the following table:
Obs | Date1 | Date2 |
---|---|---|
1 | 'StartDate1' | 1/20/2004 |
2 | 'StartDate2' | 2/13/2006 |
2 | 2/13/2006 | 3/25/2004 |
2 | 3/25/2004 | 3/30/2006 |
3 | . | . |
How can I tell SAS to create the column Date1 by grabbing values from the previous row of Date2? The StartDate's I can do based on first.Date1 = 'StartDate'. I'm just stuck on getting the rest of the values.
Thanks!
Could you use the lag() function? Something like date1=lag(date2)
David
as David said, using LAG() function:
data want;
set want;
date1=lag(date);
if obs ne lag(obs) then call missing(date1);
run;
Ksharp
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.