topic Re: select minimum value and corresponding amount in proc sql in SAS Programming

get minimum value with corresponding amount grouped by id

Suja — Fri, 09 Nov 2018 00:01:47 GMT

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!

select minimum value and corresponding amount in proc sql

Suja — Fri, 09 Nov 2018 00:08:45 GMT

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!

Re: select minimum value and corresponding amount in proc sql

Reeza — Fri, 09 Nov 2018 00:52:20 GMT

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.

Re: select minimum value and corresponding amount in proc sql

Suja — Fri, 09 Nov 2018 01:12:43 GMT

Hi Reeza,

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

Thanks

Re: select minimum value and corresponding amount in proc sql

Reeza — Fri, 09 Nov 2018 01:34:26 GMT

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.

Re: select minimum value and corresponding amount in proc sql

Andygray — Fri, 09 Nov 2018 01:45:49 GMT

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.

Re: select minimum value and corresponding amount in proc sql

singhsahab — Fri, 09 Nov 2018 03:45:44 GMT

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....

Re: select minimum value and corresponding amount in proc sql

ballardw — Fri, 09 Nov 2018 15:27:28 GMT

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;