Keeping 1st available record

[Miracle]

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

[PGStats]

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;

PG

[Miracle]

Thanks PG!!

[data_null__]

data DOB;

   set a;

   by id;

   where not missing(dob);

   if first.id;

   run;

[Miracle]

Thanks data_null_!!

[Haikuo]

SQL;

proc sql;

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

quit;

Haikuo

[Miracle]

Thanks Hai.kuo!!

[Miracle]

Much appreciated for all your help guys!!

[Miracle]

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

[PGStats]

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;

     end;

drop firstDob;

run;

PG

[Miracle]

Thanks PG for your prompt reply!

[PGStats]

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;

PG

[Miracle]

Thanks PG!