How to do simple mathematics with min/max variable in SAS

Question

I am currently running a macro code in SAS and I want to do a calculation with regards to max and min. Right now the line of code I have is :

hhincscaled = 100*(hhinc - min(hhinc) )/ (max(hhinc) - min(hhinc));
hhvaluescaled = 100*(hhvalue - min(hhvalue))/ (max(hhvalue) - min(hhvalue));

What I am trying to do is re-scale household income and value variables with the calculations below. I am trying to subtract the minimum value of each variable and subtract it from the respective maximum value and then scale it by multiplying it by 100. I'm not sure if this is the right way or if SAS is recognizing the code the way I want it.

Answer 1

I assume you are in a Data Step. A Data Step has an implicit loop over the records in the data set. You only have access to the record of the current loop (with some exceptions).

The "SAS" way to do this is the calculate the Min and Max values and then add them to your data set.

Proc sql noprint;
create table want as
select *,
       min(hhinc) as min_hhinc,
       max(hhinc) as max_hhinc,
       min(hhvalue) as min_hhvalue,
       max(hhvalue) as max_hhvalue
from have;
quit;

data want;
set want;
hhincscaled = 100*(hhinc - min_hhinc )/ (max_hhinc - min_hhinc);
hhvaluescaled = 100*(hhvalue - min_hhvalue)/ (max_hhvalue - min_hhvalue);

/*Delete this if you want to keep the min max*/
drop min_: max_:;
run;

Answer 2

Another SAS way of doing this is to create the max/min table with PROC MEANS (or PROC SUMMARY or your choice of alternatives) and merge it on. Doesn't require SQL knowledge to do, and probably about the same speed.

proc means data=have;
  *use a class value if you have one;
  var hhinc hhvalue;
  output out=minmax min= max= /autoname;
run;

data want;
  if _n_=1 then set minmax;  *get the min/max values- they will be retained automatically and available on every row;
  set have;
  *do your calculations, using the new variables hhinc_max hhinc_min etc.;
run;

If you have a class statement - ie, a grouping like 'by state' or similar - add that in proc means and then do a merge instead of a second set in want, by your class variable. It would require a sorted (initial) dataset to merge.

---

You also have the option of doing this in SAS-IML, which works more similarly to how you are thinking above. IML is the SAS interactive matrix language, and more similar to r or matlab than the SAS base language.