topic Re: Creating a list of multiple date records for each observation in SAS Procedures

Creating a list of multiple date records for each observation

JohnPura — Thu, 02 May 2013 04:31:19 GMT

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.

Re: Creating a list of multiple date records for each observation

Ksharp — Thu, 02 May 2013 04:49:33 GMT

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

Re: Creating a list of multiple date records for each observation

JohnPura — Thu, 02 May 2013 14:41:24 GMT

Thanks so much!

Re: Creating a list of multiple date records for each observation

JohnPura — Thu, 02 May 2013 15:30:12 GMT

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!

Re: Creating a list of multiple date records for each observation

DavidRice — Thu, 02 May 2013 15:52:08 GMT

Could you use the lag() function? Something like date1=lag(date2)

David

Re: Creating a list of multiple date records for each observation

Ksharp — Fri, 03 May 2013 02:34:44 GMT

as David said, using LAG() function:

data want;

set want;

date1=lag(date);

if obs ne lag(obs) then call missing(date1);

run;

Ksharp