hi everyone,
I want to know how to use a minimum or maximum value of a variable in further calculations. I am able to find out maximum or minimum values of a variable, say X, using proc univariate or proc sql. now i want to use it for further calculation say, a new variable y = (maximum of x)* observation in variable Z. I can do it putting values of minimum and maximum into the program. but i dont want to copy and paste the values from output to program. Is there any way i can do that?
proc sql;
create table WANT as
select *, y*max(x) as z
from HAVE;
quit;
I'm sure you can.
Have I understood you right: in a table have columns X, Y. For each row, you want to multiply the row value of Y with the table's maximum value of X?
Y X
10 3
12 5
8 6
10 10
Will result in
Y X Z
10 3 100
12 5 120
8 6 80
10 10 100
If this is the case SQL will do it for you in on step.
If you for some reason want to use the value of max(x) for processing in another step, you can store that value in a macro variable (see the into construct in SQL), or have the max(x) calculated in a join or in asub-query.
proc sql;
select y * max(x) as z
from inputTable;
quit;
Hi LinusH, I am able to create Z this way, thanks for your help! Can you also tell me how do you save it in data file?
If you are using PROC SQL you can use the CREATE TABLE clause to create a table (dataset).
proc sql ;
create table WANT as
select x,y,y*max(x) as z
from HAVE
;
quit;
You should also learn how to use SAS Procs to product output tables.
proc summary data=HAVE ;
var x;
output out=xmean mean=xmean ;
run;
And how to manipulate data using DATA steps. Here is a trick to copy values from a dataset with only one observation onto all observations of another dataset. _N_ is the automatic loop counter for the data step. So the record from the dataset XMEAN will only be read the first time SAS runs through the data step code. Since XMEAN variable is never overwritten again it will stay the same for all iterations of the data step.
data want;
set have ;
if _n_=1 then set xmean;
z=y*xmean;
run;
Thanks for your reply, Tom!
I did use similar code as you have given -
proc sql;
create table WANT as
select x, y, y*max(x) as z
from HAVE;
quit;
But doing this I am just able to export x, y and z to WANT. But I want to have all of the variables from HAVE and add z to it to get WANT. Can this be done?
proc sql;
create table WANT as
select *, y*max(x) as z
from HAVE;
quit;
Thank you so much Art! Its such a simple code and worked as well!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.