topic Re: Consolidating dates in rows to column in SAS Programming

Consolidating dates in rows to column

rsva — Wed, 22 Aug 2012 15:12:51 GMT

I have a data similar to one below

ID	Date
1	11jul2009
1	06jul2010
1	01jul2012
2	17jul2008
2	01jul2001
3	06jul2001
3	11jul2005
3	01jul2011
4	17jul2011
4	01jul2012
5	11jul2006
6	01jul2000
6	17jul2003
6	01jul2009
7	17jul2004

I am trying to create a data that looks like below

ID	Dt1	Dt2
1	11jul2009	01jul2012
2	17jul2008	01jul2011
3	06jul2001	01jul2011
4	17jul2011	01jul2012
5	11jul2006	11jul2006
6	01jul2000	01jul2009
7	17jul2004	17jul2004

I short I am trying to summarize the minimum and maximum dates by id. Note: the number of times an id repeats is not same. ID1 has 3 rows, ID2 has 2 rows, ID5 has 1 row. I tried the lag function but it's not giving the expected results.

Thanks.

Re: Consolidating dates in rows to column

MikeZdeb — Wed, 22 Aug 2012 15:19:46 GMT

hi ... maybe SQL ...

data x;

input id date :date9. @@;

format date date9.;

datalines;

1 11jul2009 1 06jul2010 1 01jul2012

2 17jul2008 2 01jul2001

3 06jul2001 3 11jul2005 3 01jul2011

4 17jul2011 4 01jul2012

5 11jul2006

6 01jul2000 6 17jul2003 6 01jul2009

7 17jul2004

;

proc sql;

create table minmax as

select id, min(date) as dt1 format=date9., max(date) as dt2 format=date9.

from x

group by id;

quit;

Re: Consolidating dates in rows to column

mkeintz — Wed, 22 Aug 2012 15:37:12 GMT

Since you're using the lag function, I presume you are using a DATA step, so in that vein here is the classic DATA step solution ("classic" in the sense that it's fhe first technique I became acquainted with):

data want (drop=date);

set have ;

by id;

retain mindate;

if first.id then mindate=date;

if last.id then do;

maxdate=date;

output;

end;

run;

Note this assumes there are no missing date values and the data is sorted by ID DATE.

Re: Consolidating dates in rows to column

joehinson — Wed, 22 Aug 2012 15:49:53 GMT

...and a Hash solution:

data _null_;

if (1=2) then set have;

format date1 date2 date9.; call missing(date1,date2);

declare hash dt (ordered:'a');

dt.defineKey("id");

dt.defineData("id","date1","date2");

dt.defineDone();

do until(done);

set have end=done;

by id notsorted;

if first.id then do;

date1=date;

rc=dt.replace();

end;

if last.id then do;

date2=date;

rc=dt.replace();

end;

dt.output(dataset:"want");

stop;

run;

Re: Consolidating dates in rows to column

rsva — Wed, 22 Aug 2012 16:17:47 GMT

Thanks. But my data has dates in rows not in multiple columns.

Re: Consolidating dates in rows to column

rsva — Wed, 22 Aug 2012 16:18:49 GMT

It worked. Lukcily I do not have missing data. Any suggestons if my data has missing values? Just curious?

Thanks.

Re: Consolidating dates in rows to column

MikeZdeb — Wed, 22 Aug 2012 16:25:39 GMT

Hi ... my solution creates a data set with dates in one variable named DATE (the way the raw data looks in the data step has no relationship to the way the data set looks)

(curious ... do you know how an INPUT statement that ends with "@@" works)

also, MISSING values would present no problem with the SQL solution I posted

data x;

input id date :date9. @@;

format date date9.;

datalines;

1 11jul2009 1 06jul2010 1 01jul2012

2 17jul2008 2 01jul2001

3 06jul2001 3 11jul2005 3 01jul2011

4 17jul2011 4 01jul2012

5 11jul2006

6 01jul2000 6 17jul2003 6 01jul2009

7 17jul2004

;

proc print data=x;

run;

Obs id date

1 1 11JUL2009

2 1 06JUL2010

3 1 01JUL2012

4 2 17JUL2008

5 2 01JUL2001

6 3 06JUL2001

7 3 11JUL2005

8 3 01JUL2011

9 4 17JUL2011

10 4 01JUL2012

11 5 11JUL2006

12 6 01JUL2000

13 6 17JUL2003

14 6 01JUL2009

15 7 17JUL2004

Re: Consolidating dates in rows to column

joehinson — Wed, 22 Aug 2012 17:07:40 GMT

Certainly. Works with missing numbers too:

data have;

input id date :date9.;

datalines;

1 11jul2009

1 06jul2010

1 01jul2012

2 17jul2008

2 01jul2001

3 .

4 17jul2011

4 .

5 11jul2006

6 01jul2000

6 .

7 17jul2004

;

run;

RESULTS:

1	11JUL2009	01JUL2012
2	17JUL2008	01JUL2001
3	.	.
4	17JUL2011	.
5	11JUL2006	11JUL2006
6	01JUL2000	.
7	17JUL2004	17JUL2004

Re: Consolidating dates in rows to column

rsva — Wed, 22 Aug 2012 18:05:02 GMT

MikeZdeb,

My mistake. I overlooked the @@ in the INPUT statement. It worked.

Re: Consolidating dates in rows to column

mkeintz — Wed, 22 Aug 2012 18:59:43 GMT

If you have a missing value for date, and if the data are sorted by ID then DATE, it would mean that the first date(s) is missing (all missing values compare as lower than the most negative valid number).

So you could change the single line

if first.id then mindate=date;

to two lines:

if first.id then mindate=date;

if mindate=. then mindate=date;