Solved
Contributor
Posts: 27

Creating a list of multiple date records for each observation

I have a list of observations and for each observation, I have a several dates, so that it looks something like this:

ObsDates
11/20/2004
23/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:

ObsDates
11/20/2004
23/25/2004
22/13/2006
23/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.

Accepted Solutions
Solution
‎05-02-2013 12:49 AM
Super User
Posts: 10,784

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

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

All Replies
Solution
‎05-02-2013 12:49 AM
Super User
Posts: 10,784

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

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

Contributor
Posts: 27

Thanks so much!

Contributor
Posts: 27

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

As a followup, how can I create the following table:

ObsDate1Date2
1'StartDate1'1/20/2004
2'StartDate2'2/13/2006
22/13/20063/25/2004
23/25/20043/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!

Occasional Contributor
Posts: 5

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

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

David

Super User
Posts: 10,784

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

as David said, using LAG() function:

data want;

set want;

date1=lag(date);

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

run;

Ksharp

🔒 This topic is solved and locked.