topic Re: Keeping 1st available record in SAS Programming

Keeping 1st available record

Miracle — Sat, 04 Aug 2012 07:29:45 GMT

Hi everyone.

This may be a simple question but I've been struggling for hours to do. The question is I'd like to keep only the earlist available dob for every individual ie from A to B.

Your help is greatly appreaciated! Thanks in advance!

From A

id childid dob

1 1 07/08/1980

1 2 04/04/1985

1 3 12/12/1987
2 4 .

2 5 01/02/1990

3 6 .

3 7 .

3 8 25/12/1995

to B

id childid dob

1 1 07/08/1980

2 5 01/02/1990

3 8 25/12/1995

Re: Keeping 1st available record

PGStats — Sat, 04 Aug 2012 12:06:47 GMT

data a;
input id    childid    dob :ddmmyy10.;
format dob ddmmyy10.;
datalines;
1     1            07/08/1980
1     2            04/04/1985
1     3           12/12/1987
2     4            .
2     5            01/02/1990
3     6            .
3     7            .
3     8            25/12/1995
;

data B;
do until (last.id);
     set a; by id;
     if missing(firstDob) and not missing(dob) then do;
          firstDob = dob;
          output;
          end;
     end;
drop firstDob;
run;

Re: Keeping 1st available record

data_null__ — Sat, 04 Aug 2012 12:54:10 GMT

data DOB;

set a;

by id;

where not missing(dob);

if first.id;

run;

Re: Keeping 1st available record

Haikuo — Sat, 04 Aug 2012 13:40:25 GMT

SQL;

proc sql;

select * from a group by id having dob=min(dob);

quit;

Haikuo

Re: Keeping 1st available record

Miracle — Sun, 05 Aug 2012 09:43:10 GMT

Much appreciated for all your help guys!!

Re: Keeping 1st available record

Miracle — Sun, 05 Aug 2012 09:45:24 GMT

Thanks PG!!

Re: Keeping 1st available record

Miracle — Sun, 05 Aug 2012 09:45:49 GMT

Thanks data_null_!!

Re: Keeping 1st available record

Miracle — Sun, 05 Aug 2012 09:46:24 GMT

Thanks Hai.kuo!!

Re: Keeping 1st available record

Miracle — Mon, 06 Aug 2012 01:09:03 GMT

Hi everyone.

I've tried the ways you all suggested.

However, in order to keep the 1st childid if a woman had twins or all of her childdob missing ie id=4 and 5, I added a datastep with "if first.id". I wonder if this's nessary or such condition can be taken into account without having to write any extra datastep or SAS procedure.

Thanks again in advance!!

From A

id childid dob

1 1 07/08/1980

1 2 04/04/1985

1 3 12/12/1987
2 4 .

2 5 01/02/1990

3 6 .

3 7 .

3 8 25/12/1995

4 9 .

4 10 .

5 11 08/08/1998

5 12 08/08/1998

to B

id childid dob

1 1 07/08/1980

2 5 01/02/1990

3 8 25/12/1995

4 9 .

5 11 08/08/1998

Re: Keeping 1st available record

PGStats — Mon, 06 Aug 2012 01:42:45 GMT

A simple modification does the trick :

data B;

do until (last.id);

set A; by id;

if missing(firstDob) and (last.id or not missing(dob)) then do;

firstDob = dob;

output;

end;

drop firstDob;

run;

Re: Keeping 1st available record

Miracle — Mon, 06 Aug 2012 01:49:48 GMT

Thanks PG for your prompt reply!

Re: Keeping 1st available record

PGStats — Mon, 06 Aug 2012 02:13:35 GMT

You will notice a possible weakness in my solution : it is the last childid that is reported when there are no dob available for a given id. A modification of Reeza's query gives the lowest childid (maybe not the first in the list) :

proc sql;

create table B as

select id, min(childid) as childid, min(dob) as dob format=ddmmyy10.

from (select id, childid, dob from A group by id having dob=min(dob))

group by id;

quit;

Re: Keeping 1st available record

Miracle — Mon, 06 Aug 2012 02:51:36 GMT

Thanks PG!