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
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
Thanks PG!!
data DOB;
set a;
by id;
where not missing(dob);
if first.id;
run;
Thanks data_null_!!
SQL;
proc sql;
select * from a group by id having dob=min(dob);
quit;
Haikuo
Thanks Hai.kuo!!
Much appreciated for all your help guys!!
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
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
Thanks PG for your prompt reply!
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
Thanks PG!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.