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!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: