get minimum value with corresponding amount grouped by id

[Suja]

Hi Guys,

I want to get minimum age for each age variable within the group of id and their corresponding amount. How can I achieve this using proc sql or proc sort/data step?

My below proc sql is getting the minimum age for the each group but don't know how to get the corresponding amount.

data test;
input id cc_age cc_amt PL_age PL_amt Mort_age Mort_amt bank_age bank_amt;

cards;

001 2 1000 . . 8 1600 11 300

001 12 1000 6 3000 . . 6 1300

001 . . 3 4000 . . 4 2430

001 7 900 . . 2 2999 . .

002 5 4000 9 5000 11 2000 . .

002 . . 4 1500 . . 8 990

002 9 900 . . 7 890 10 2600
;
run;

proc sql;
create table test1 as select id, min(cc_age) as min_cc_age, min(pl_age) as min_pl_age, min(mort_age) as min_mort_age, min(bank_age) as min_bank_age
from test
group by id;
quit;

Current Result:
id min_cc_age min_pl_age min_mort_age min_bank_age
1  2                  3                 2                        4
2  5                  4                 7                        8

Expected Result:

id min_cc_age    cc_amt      min_pl_age   pl_amt    min_mort_age    mort_amt    min_bank_age     bank_amt
1  2                    1000           3                  4000        2                        2999            4                           2430
2  5                   4000            4                 1500         7                        890              8                           990

Thanks in advance for all your help!

[Suja]

Hi Guys,

I want to get the minimum value for each variable within the group and also get the corresponding amount. How can I achieve this using proc sql or proc sort/data step?

My below code is getting the minimum age for the each group but don't know how to get the corresponding amount.

data test;
input id cc_age cc_amt PL_age PL_amt Mort_age Mort_amt bank_age bank_amt;

cards;

001 2 1000 . . 8 1600 11 300

001 12 1000 6 3000 . . 6 1300

001 . . 3 4000 . . 4 2430

001 7 900 . . 2 2999 . .

002 5 4000 9 5000 11 2000 . .

002 . . 4 1500 . . 8 990

002 2 900 . . 7 890 10 2600
;
run;

proc sql;
create table test1 as select id, min(cc_age) as min_cc_age, min(pl_age) as min_pl_age, min(mort_age) as min_mort_age, min(bank_age) as min_bank_age
from test
group by id;
quit;

Current Result:
id min_cc_age min_pl_age min_mort_age min_bank_age
1 2                   3                  2                      4
2 5                   4                  7                      8

Expected Result:

id min_cc_age cc_amt  min_pl_age pl_amt        min_mort_age   mort_amt    min_bank_age    bank_amt
1  2                 1000       3                 4000            2                       2999           4                          2430
2 5                  4000       4                 1500            7                        890             8                         990

Really appreciate all your help. Thanks in advance!

[Reeza]

You’d be better if transposing this so you had three columns, age, amount and type where type is now your differentiator. Then you can use PROC SUMMARY with MINID.

[Suja]

Hi Reeza,

Thanks for your quick response. Could you please provide an example?

Thanks

[Reeza]

This is a personal pet annoyance, but I don't respond to people who address their question to 'guys' - I missed it on the first read. Sorry, I cannot help you since I'm not a guy. I usually don't even bother to address it and ignore those question entirely, but since I already responded once I'll mention it here and walk away. Good Luck.

[Andygray]

+

I personally have noticed besides the address of "hi guys", you lack etiquette in terms of not acknowledging answers, not marking them, very biased responses in several other threads and beyond. It gives the impression, getting answer is some kinda right than a humble privilege. Although it's my opinion, I'm entitled to it and I care the least whomsoever disagrees. 

[singhsahab]

Hello Suja,

i found that there is some contradiction in your request and excepted output. 

If you want minimum value by each id group. you can use proc means. 

data test;
input id cc_age cc_amt PL_age PL_amt Mort_age Mort_amt bank_age bank_amt;
cards;
001 2 1000 . . 8 1600 11 300
001 12 1000 6 3000 . . 6 1300
001 . . 3 4000 . . 4 2430
001 7 900 . . 2 2999 . .
002 5 4000 9 5000 11 2000 . .
002 . . 4 1500 . . 8 990
002 2 900 . . 7 890 10 2600
;
run;

proc means data=test min maxdec=0;
class id;
run;

you can use output options to create output data set, if want to include missing observation on calculation . kindly use missing options .

Thanks....

[ballardw]

data test;
input id cc_age cc_amt PL_age PL_amt Mort_age Mort_amt 
      bank_age bank_amt;
cards;
001 2 1000 . . 8 1600 11 300
001 12 1000 6 3000 . . 6 1300
001 . . 3 4000 . . 4 2430
001 7 900 . . 2 2999 . .
002 5 4000 9 5000 11 2000 . .
002 . . 4 1500 . . 8 990
002 9 900 . . 7 890 10 2600
;
run;

data trans;
   set test;
   length Type $ 5;
   type='CC'; age=cc_age; amt=cc_amt;output;
   type='PL'; age=PL_age; amt=PL_amt;output;
   type='MORT'; age=MORT_age; amt=MORT_amt;output;
   type='BANK'; age=BANK_age; amt=BANK_amt;output;
   keep id type age amt;
run;

/* report for people to read*/
proc tabulate data=trans;
  class id type;
  var age amt;
  table id,
        type=''*(Age Amt)*min=*f=best5.
        /misstext=' ';
run;
/* data set for further manipulation*/
proc summary data=trans nway;
   class id type;
   var age amt;
   output out=want (drop= _:) min=;
run;