topic Re: Creating a new dataset with the highest value from an existing dataset in SAS Procedures

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

Sapkota — Wed, 03 Dec 2014 19:02:36 GMT

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.

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

Haikuo — Wed, 03 Dec 2014 19:33:43 GMT

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;

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

ballardw — Wed, 03 Dec 2014 20:26:45 GMT

proc summary data=have nway;

class id;

var weight;

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

run;

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

Sapkota — Wed, 03 Dec 2014 20:49:05 GMT

Thank you. This is helpful.

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

data_null__ — Wed, 03 Dec 2014 21:10:46 GMT

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;

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

KenDodds — Wed, 03 Dec 2014 21:50:16 GMT

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;

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

Sapkota — Thu, 04 Dec 2014 14:36:38 GMT

Thanks very much.