topic Re: Using the max of a variable to generate a new variable in New SAS User

Using the max of a variable to generate a new variable

raivester — Wed, 28 Apr 2021 19:47:35 GMT

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?

Re: Using the max of a variable to generate a new variable

Reeza — Wed, 28 Apr 2021 20:01:11 GMT

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?

Re: Using the max of a variable to generate a new variable

ballardw — Wed, 28 Apr 2021 20:03:16 GMT

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.