I have a dataset that looks like this -
data try_1;
input ID c_ID d_status age fin_grp;
datalines;
1 1 1 6 1
1 2 1 12 1
1 3 1 15 1
2 1 2 10 0
2 2 2 14 0
2 3 2 16 0
4 1 1 6 1
4 2 2 13 1
4 3 2 15 1
5 1 1 9 0
5 2 2 12 0
5 3 2 11 0
;
run;
For each group, I want to select one c_id per ID in two stages.
In the first stage, I need to decide among d_status values based on value for fin_grp.
Thus for fin_grp=1, I need to select from d_status=1 AND for fin_grp=0, I need to select from d_status not equal to 1.
In the second stage, withing the above selected d_status, want to select a one c_id with has minimum age.
eg. For ID=5, I would like to select c_id=3 which has d_status=2 and minimum age.
Can anyone help?
data try_1; input ID c_ID d_status age fin_grp; datalines; 1 1 1 6 1 1 2 1 12 1 1 3 1 15 1 2 1 2 10 0 2 2 2 14 0 2 3 2 16 0 4 1 1 6 1 4 2 2 13 1 4 3 2 15 1 5 1 1 9 0 5 2 2 12 0 5 3 2 11 0 ; run; proc sql; create table want as select * from try_1 where (fin_grp=1 and d_status=1) or (fin_grp=0 and d_status ne 1) group by id having age=min(age); quit;
Xia Keshan
data try_1; input ID c_ID d_status age fin_grp; datalines; 1 1 1 6 1 1 2 1 12 1 1 3 1 15 1 2 1 2 10 0 2 2 2 14 0 2 3 2 16 0 4 1 1 6 1 4 2 2 13 1 4 3 2 15 1 5 1 1 9 0 5 2 2 12 0 5 3 2 11 0 ; run; proc sql; create table want as select * from try_1 where (fin_grp=1 and d_status=1) or (fin_grp=0 and d_status ne 1) group by id having age=min(age); quit;
Xia Keshan
Thanks a lot.
That was really helpful
data try_1;
input ID c_ID d_status age fin_grp;
datalines;
1 1 1 6 1
1 2 1 12 1
1 3 1 15 1
2 1 2 10 0
2 2 2 14 0
2 3 2 16 0
4 1 1 6 1
4 2 2 13 1
4 3 2 15 1
5 1 1 9 0
5 2 2 12 0
5 3 2 11 0
;
data want (drop=age1 c_id1);
age1=.;
do until(last.id);
set try_1;
by id;
where (fin_grp=1 and d_status=1) or (fin_grp=0 and d_status ne 1);
age1= min(age1,age);
if age<=age1 then c_id1=c_id;
else if age>age1 then c_id=c_id1;
end;
age=age1;
run;
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.