BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sapkota
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

6 REPLIES 6
Haikuo
Onyx | Level 15

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;


Sapkota
Calcite | Level 5

Thank you. This is helpful.

ballardw
Super User

OR

proc summary data=have nway;

class id;

var weight;

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

run;

data_null__
Jade | Level 19

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
KenDodds
Calcite | Level 5

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;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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