rearranging data to select ID with min value of a variable

[ritup]

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?

[Ksharp · Accepted solution]

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

[ritup]

Thanks a lot.

That was really helpful

[naveen_srini]

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;