Using the max of a variable to generate a new variable

[raivester]

I am wondering if there is a way to generate a new variable using the max of an existing variable. For example, I may have a data like:

var1  var2

2       10

3       20

5       10

1       25

7       20

and I want to generate a new variable in this data set called var3 such that var3 = var1/max(var2). The resulting data set would look like:

var1  var2  var3

2       10     .08

3       20     .12

5       10     .20

1       25     .04

7       20     .28

How would I do this?

[Reeza]

SQL is pretty straightforward.

proc sql;
create table example as
select *, age/max(age) as var3
from sashelp.class;
quit;

******************************************************;
*Add average value to a dataset;
*Solution 1 - PROC MEANS + Data step;
******************************************************;

proc means data=sashelp.class noprint;
    output out=avg_values mean(height)=avg_height;
run;

data class_data;
    set sashelp.class;

    if _n_=1 then
        set avg_values;
run;

proc print data=class;
run;

*Solution 2 - PROC SQL - note the warning in the log;
PROC SQL;
Create table class_sql as
select *, mean(height) as avg_height
from sashelp.class;
quit;

******************************************************;
*Add average value to a dataset - with grouping variables;
*Solution 1 - PROC MEANS + Data step;
******************************************************;
proc means data=sashelp.class noprint nway;
class sex;
    output out=avg_values mean(height)=avg_height;
run;

*sort data before merge;
proc sort data=sashelp.class out=class;
by sex;
run;

data class_data;
 merge class avg_values;
 by sex;


run;

proc print data=class_data;
run;

*Solution 2 - PROC SQL - note the warning in the log;
PROC SQL;
Create table class_sql as
select *, mean(height) as avg_height
from sashelp.class
group by sex;
quit;

Other options include merging in the max and doing it via a data step or using a DoW loop. I'd consider a DoW loop a little beyond your skill set at the moment. 

Here are examples of how to add in mean, which can easily be translated to a maximum. 

---

@raivester wrote:

I am wondering if there is a way to generate a new variable using the max of an existing variable. For example, I may have a data like:

var1  var2

2       10

3       20

5       10

1       25

7       20

and I want to generate a new variable in this data set called var3 such that var3 = var1/max(var2). The resulting data set would look like:

var1  var2  var3

2       10     .08

3       20     .12

5       10     .20

1       25     .04

7       20     .28

How would I do this?

---

[ballardw]

One way:

Note the data step that creates actual data set to code with (Hint.);

data have;
   input var1  var2;
datalines;
2       10
3       20
5       10
1       25
7       20
;

proc sql;
   create table want as
   select a.var1, a.var2, a.var1/b.maxvar2 as ratio
   from have as a ,
        (select max(var2) as maxvar2 from have) as b
   ;
quit;

Depending on other bits in code Proc SQL may reorder values.

The above code uses  (select max(var2) as maxvar2 from have) to find the maximum value and creates and alias of B to reference the value set created. The "from have as a, " uses data set Have and matches each record in the referenced A alias with all the B values (only one that there is). The comma between to sets performs a cartesian join, the fancy term for matching all against all. Because there isn't any way to reduce this you will see a note in the log that the operation cannot be optimized.