topic Re: Find month where get max value in SAS Programming

Find month where get max value

Ronein — Sun, 06 Oct 2024 20:15:09 GMT

Hello

For each customer have 6 rows with information of 6 months on wealth and obligo.

Target-

to calculate for each customer which month (1/2/3/4/5/6) provide max wealth

to calculate for each customer which month (1/2/3/4/5/6) provide max obligo

what is the way to create wanted data set from have data set?

note- IF multiple months have max value then the max month will be the earliest



data have;
input CustID  YYYYMM month wealth obligo;
cards;
1111 202401 1 100 20
1111 202402 2 140 80
1111 202403 3 180 15
1111 202404 4 110 12
1111 202405 5 100 12
1111 202406 6 130 10
2222 202401 1 300 13
2222 202402 2 140 14
2222 202403 3 180 15
2222 202404 4 110 12
2222 202405 5 100 40
2222 202406 6 130 10
3333 202401 1 300 10
3333 202402 2 140 14
3333 202403 3 180 15
3333 202404 4 110 40
3333 202405 5 100 40
3333 202406 6 300 10
;
Run;


data wanted;
input CustID month_max_wealth month_max_obligo;
cards;
1111 3 2
2222 1 5
3333 1 4
;
Run;

Re: Find month where get max value

PaigeMiller — Sun, 06 Oct 2024 20:58:01 GMT

PROC SUMMARY is the way

proc summary data=have nway;
    class custid;
    var wealth obligo;
    output out=want maxid(wealth(month) obligo(month))=max_wealth max_obligo;
run;

Re: Find month where get max value

Ksharp — Mon, 07 Oct 2024 01:45:50 GMT

data have;
input CustID  YYYYMM  $ month wealth obligo;
cards;
1111 202401 1 100 20
1111 202402 2 140 80
1111 202403 3 180 15
1111 202404 4 110 12
1111 202405 5 100 12
1111 202406 6 130 10
2222 202401 1 300 13
2222 202402 2 140 14
2222 202403 3 180 15
2222 202404 4 110 12
2222 202405 5 100 40
2222 202406 6 130 10
3333 202401 1 300 10
3333 202402 2 140 14
3333 202403 3 180 15
3333 202404 4 110 40
3333 202405 5 100 40
3333 202406 6 300 10
;
Run;
data want;
do until(last.CustID);
 set have;
 by CustID;
 if wealth>max_wealth then do;month_max_wealth=month;max_wealth=wealth;end;  
 if obligo>max_obligo then do;month_max_obligo=month;max_obligo=obligo;end;  
end;
keep CustID max_: month_:;
run;

Re: Find month where get max value

Kurt_Bremser — Mon, 07 Oct 2024 09:45:23 GMT

Maxim 7 at work. Very nice.

Re: Find month where get max value

PaigeMiller — Mon, 07 Oct 2024 10:00:45 GMT

And Maxim 14!