Help using Base SAS procedures

Creating a list of multiple date records for each observation

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

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: 9,691

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

View solution in original post


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

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

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

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: 9,691

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 358 views
  • 3 likes
  • 3 in conversation