how to find max value of variable for each unique observation in "stacked" dataset

Question

hi, sorry for the vauge title.

My data set looks essentially like this:

ID X 18 1 18 1 18 2 18 1 18 2 369 2 369 3 369 3 361 1

what I want is to find the max value of x for each ID. In this dataset, that would be 2 for ID=18 and 3 for ID=361.

Any feedback would be greatly appreciated. thanks

Answer 1

I don't quite understand your example. I can't imagine that the input data set really has all the values in one observation. Do you instead mean something like this?

data sample;
    input myid myvalue;
datalines;
18  1
18  1
18  2
18  1
18  2
369 2
369 3
369 3
361 1
;

proc sort data=sample;
    by myid myvalue;
run;

data result;
    set sample;
    by myid;

    if last.myid then output;
run;

proc print data=result;
run;

This would give you this result:

Obs    myid    myvalue

 1       18       2   
 2      361       1   
 3      369       3   

Answer 2

Use an appropriate proc with the by statement. For instance,

data sample;
    input myid myvalue;
datalines;
18  1
18  1
18  2
18  1
18  2
369 2
369 3
369 3
361 1
;
run;

proc sort data=sample;
  by myid;
run;

proc means data=sample;
   var myvalue;
   by myid;
run;

Answer 3

Proc Means with a class statement (so you don't have to sort) and requesting the max statistic is probably the most straightforward approach (untested):

data sample; 
    input id x; 
datalines; 
18  1 
18  1 
18  2 
18  1 
18  2 
369 2 
369 3 
369 3 
361 1 
; 
run; 


proc means data=sample noprint max nway missing; 
   class id;
   var x;
   output out=sample_max (drop=_type_ _freq_) max=;
run; 

Check out the online SAS documentation for more details on Proc Means (http://support.sas.com/onlinedoc/913/docMainpage.jsp).