How to apply a mathematical expression to all columns of a table in SAS?

Question

The expression will be applied to each of the 747 columns of a table/dataset. It follows the expression: (variable - variable average) / (maximum variable value - minimum variable value).

Will be executed in the SAS.

Answer 1

The "proc standardize" procedure, called by the command PROC STDIZE, allows several forms of standardization of the numerical variables of a dataset/table in SAS. For the complete documentation of the procedure, see this link.

Each of the PROC STDIZE methods assigns different values to the parameters LOCAL (place) and SCALE (scale). These parameters are then combined with the original variables according to the function below (see pages 5-6 of the documentation):

resultado = soma + multiplicação x (original - local)/escala

You can verify that none of the standard methods of PROC STDIZE automatically performs the transformation you want. Then, the solution is to combine two of these procedures, the MEAN: that saves the average of the columns as a local variable; and RANGE: which saves the width of the columns as a scale variable (see pages 21 and 22 of the documentation). This can be done using the code:

* Salva as médias das variáveis numéricas;
proc stdize data=have method=mean out=out1 outstat=medias;
var _numeric_;
run;

* Salva a amplitude das variáveis numéricas;
proc stdize data=have method=range out=out1 outstat=amplitude;
var _numeric_;
run;

* Cria uma tabela com as médias como medida "local" e a amplitude como medidas de "escala";
data local_e_escala;
set medias (where=(_type_='LOCATION')) amplitude (where=(_type_='SCALE'));
run;

* Cria novo método de padronização utilizando a tabela criada como input; 
proc stdize data=have method=in(local_e_escala) out=want;
var _numeric_;
run;

Answer 2

Hello

In SAS there is a library called SASHELP with views that can help in various situations One of them is called VCOLUMN, which contains the names of all the columns of each table. You can filter this view and select the variables you want to use and create a loop macro to do the calculations.