Hello
For each person ID I have multiple observations (multiple rows).
Target is to find max weight for each customer over his/her observations.
Following code is giving bad results.
why?
Data rawData;
Input ID month weight;
cards;
1 1801 70
1 1802 90
1 1803 85
2 1801 90
2 1802 94
2 1803 93
3 1802 78
3 1803 80
;
run;
Data tbl1(keep=ID max_weight) ;
set rawData;
by ID;
retain max_weight 0 ;
max_weight=max(max_weight,weight);
IF last.ID then output;
Run;
I assume by "bad results" you mean it is not giving you what you expected it to give you? It is doing what you ask correctly, your just not covering the whole by group, so the value gets retained all the way down, so each row is the maximum of the whole table to that point, not just within the by group. To do the by group you need to reset your retained value on first.id:
data tbl1(keep=id max_weight) ; set rawdata; by id; retain max_weight; if first.id then max_weight=0; max_weight=max(max_weight,weight); if last.id then output; run;
Or if you want smaller code:
data tbl1(keep=id max_weight) ; set rawData; by ID; retain max_weight; max_weight=ifn(first.id,weight,max(max_weight,weight)); if last.id then output; run;
I think you forgot to initialize the MAX_WEIGHT variable when the ID changes. Or you could use a DoW loop without a retain statement:
data tbl1(keep=ID max_weight) ;
do until(last.id);
set rawData;
by ID;
max_weight=max(max_weight,weight);
end;
run;
I assume by "bad results" you mean it is not giving you what you expected it to give you? It is doing what you ask correctly, your just not covering the whole by group, so the value gets retained all the way down, so each row is the maximum of the whole table to that point, not just within the by group. To do the by group you need to reset your retained value on first.id:
data tbl1(keep=id max_weight) ; set rawdata; by id; retain max_weight; if first.id then max_weight=0; max_weight=max(max_weight,weight); if last.id then output; run;
Or if you want smaller code:
data tbl1(keep=id max_weight) ; set rawData; by ID; retain max_weight; max_weight=ifn(first.id,weight,max(max_weight,weight)); if last.id then output; run;
May i ask why not a simple proc means/summary/sql?
@Ronein wrote:
Hello
For each person ID I have multiple observations (multiple rows).
Target is to find max weight for each customer over his/her observations.
Following code is giving bad results.
why?
Data rawData; Input ID month weight; cards; 1 1801 70 1 1802 90 1 1803 85 2 1801 90 2 1802 94 2 1803 93 3 1802 78 3 1803 80 ; run; Data tbl1(keep=ID max_weight) ; set rawData; by ID; retain max_weight 0 ; max_weight=max(max_weight,weight); IF last.ID then output; Run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: