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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.