topic rearranging data to select ID with min value of a variable in SAS Data Management

rearranging data to select ID with min value of a variable

ritup — Thu, 15 Jan 2015 07:25:41 GMT

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?

Re: rearranging data to select ID with min value of a variable

Ksharp — Thu, 15 Jan 2015 08:09:33 GMT

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

Re: rearranging data to select ID with min value of a variable

ritup — Thu, 15 Jan 2015 09:02:28 GMT

Thanks a lot.

That was really helpful

Re: rearranging data to select ID with min value of a variable

naveen_srini — Thu, 15 Jan 2015 11:37:27 GMT

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;