Help using Base SAS procedures

Creating a new dataset with the highest value from an existing dataset

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Creating a new dataset with the highest value from an existing dataset

I have two different values of body-weight for each individuals in a dataset (Dataset 1) and I would like to create a new dataset (Dataset 2) that will have only the highest of the two values. I was wondering how do I write a SAS code for that. My current dataset is:

Dataset 1

-------------

--------------------------

ID               Weight

---               -----------

A101W1    140 lb

A101W2    142 lb

A102W1    145 lb

A102W2    144 lb

A103W1     139lb

A103W2     140lb

A104W1      144lb

A104W2      145lb

-----------       ----------

and so on     and so on

I would like to create a SAS code for creating a new dataset (Dataset 2) that will contain the highest weight value out of the two from each individual in Dataset 1.

Dataset 2

-------------

ID                   Weight

----                  -------------

A101W2       142 lb

A102W1       145 lb

A103W2       140 lb

A104W2       145 lb

and so on     and so on

Any help will be highly appreciated. And thanks in advance.


Accepted Solutions
Solution
‎12-03-2014 04:10 PM
Respected Advisor
Posts: 3,777

Re: Creating a new dataset with the highest value from an existing dataset

You're close but you need to format ID properly.  Proc summary will do it all but you do need to remove the format from ID to see the Wn part again.

data have;
   input ID$    Weight;
   cards;
A101W1    140 lb
A101W2    142 lb
A102W1    145 lb
A102W2    144 lb
A103W1    139 lb
A103W2    140 lb
A104W1    144 lb
A104W2    145 lb
;;;;
   run;
proc summary data=have nway;
  
class id;
   format id $4.;
  
output out=max(drop=id _: rename=(id2=ID)) idgroup(max(weight) out(id weight)=id2);
   run;
proc datasets nolist;
  
modify max;
   format id;
   run;
  
quit;
proc print;
  
run;

12-3-2014 3-09-05 PM.png

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: Creating a new dataset with the highest value from an existing dataset

You could try data step 'look ahead' or 'look behind' technique, but for me it is easier to do the following:

data have;

     input ID$    Weight;

/*   grp+mod(_n_,2)=1;*/

length grp $ 4;

grp=id;

     cards;

A101W1    140 lb

A101W2    142 lb

A102W1    145 lb

A102W2    144 lb

A103W1    139 lb

A103W2    140 lb

A104W1      144 lb

A104W2      145 lb

;

proc sql;

     create table want as

           select * from have group by grp having weight=max(weight);

quit;


Contributor
Posts: 32

Re: Creating a new dataset with the highest value from an existing dataset

Thank you. This is helpful.

Super User
Posts: 10,516

Re: Creating a new dataset with the highest value from an existing dataset

OR

proc summary data=have nway;

class id;

var weight;

output out=want(drop=_type_ _freq_) max=;

run;

Solution
‎12-03-2014 04:10 PM
Respected Advisor
Posts: 3,777

Re: Creating a new dataset with the highest value from an existing dataset

You're close but you need to format ID properly.  Proc summary will do it all but you do need to remove the format from ID to see the Wn part again.

data have;
   input ID$    Weight;
   cards;
A101W1    140 lb
A101W2    142 lb
A102W1    145 lb
A102W2    144 lb
A103W1    139 lb
A103W2    140 lb
A104W1    144 lb
A104W2    145 lb
;;;;
   run;
proc summary data=have nway;
  
class id;
   format id $4.;
  
output out=max(drop=id _: rename=(id2=ID)) idgroup(max(weight) out(id weight)=id2);
   run;
proc datasets nolist;
  
modify max;
   format id;
   run;
  
quit;
proc print;
  
run;

12-3-2014 3-09-05 PM.png
Occasional Contributor
Posts: 7

Re: Creating a new dataset with the highest value from an existing dataset

Another way ...

data have;
  
input ID$   Weight;
   uniqid = SUBSTR(ID,
1,4);
  
cards;
A101W1    140 lb
A101W2    142 lb
A102W1    145 lb
A102W2    144 lb
A103W1    139 lb
A103W2    140 lb
A104W1    144 lb
A104W2    145 lb
;;;;
  
run;
PROC SORT; BY uniqid weight;
DATA have;
SET have;
BY uniqid;
IF LAST.uniqid;
DROP uniqid;
PROC PRINT;
RUN;


Contributor
Posts: 32

Re: Creating a new dataset with the highest value from an existing dataset

Thanks very much.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 282 views
  • 8 likes
  • 5 in conversation