BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ritup
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

View solution in original post

3 REPLIES 3
Ksharp
Super User
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
Calcite | Level 5

Thanks a lot.

That was really helpful

naveen_srini
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 3 replies
  • 928 views
  • 0 likes
  • 3 in conversation