topic Re: transforming large number of variables with sas macro in SAS Programming

transforming large number of variables with sas macro

cvan-hull — Wed, 27 Jun 2018 19:01:31 GMT

Hi, I am very new to programming in SAS. I have a relatively simple problem that I haven't been able to solve. I want to log transform >1000 variables. I'm trying to use a sas macros to

a) generate a list of all the variables that I want to transform

b) calculate the transformed variable and call them prefix.oldvarname.

I used the following script to create a list of variable names (vlist) but apparently its just an array of text and not a list of variables. Any suggestions? Thanks.

%macro getvars(dsn);
    %global vlist;
    proc sql;
        select name into :vlist separated by ' '
        from dictionary.columns
        where memname = upcase("&dsn") and TYPE="num";
    quit;
%mend;

Re: transforming large number of variables with sas macro

Kurt_Bremser — Wed, 27 Jun 2018 19:09:48 GMT

You can create a table with the variable names instead of a macro variable, and then use call execute to create dynamic code off that table.

@cvan-hull wrote:

Hi, I am very new to programming in SAS. I have a relatively simple problem that I haven't been able to solve. I want to log transform >1000 variables. I'm trying to use a sas macros to

a) generate a list of all the variables that I want to transform

b) calculate the transformed variable and call them prefix.oldvarname.

I used the following script to create a list of variable names (vlist) but apparently its just an array of text and not a list of variables. Any suggestions? Thanks.

%macro getvars(dsn);
    %global vlist;
    proc sql;
        select name into :vlist separated by ' '
        from dictionary.columns
        where memname = upcase("&dsn") and TYPE="num";
    quit;
%mend;

Re: transforming large number of variables with sas macro

Kurt_Bremser — Wed, 27 Jun 2018 19:09:08 GMT

PS show us the code you use for transforming one variable. It will be the starting point.

Re: transforming large number of variables with sas macro

Astounding — Wed, 27 Jun 2018 19:17:48 GMT

With 1,000 variables, your transformation code would likely fit into a single macro variable. (If most variable names are lengthy, there could be a problem since macro variables have an upper limit on length.) Instead of this:

select name into : ...............................

Put all the rest of the formula into your selection:

select catt("myprefix_", name, "= log(", name, ");") into : ............................................

Then you can utilize the generated code:

data want;

set have;

&vlist

run;

Re: transforming large number of variables with sas macro

ballardw — Wed, 27 Jun 2018 19:29:10 GMT

Might be time to consider if all of the variables are needed or if the data should be in a different structure.

If you have variables that measure the same thing but are taken at different times so that you have Measure_2010, Measure 2011, Measure_2012... then perhaps it is time to consider have a variable for when measures are taken then having one record per measure

id variables measure_year Measure.

Now you would only have 1 variable, Measure, instead of umpteen Measure variables to transform.

Re: transforming large number of variables with sas macro

PGStats — Wed, 27 Jun 2018 20:42:48 GMT

This might do:

%macro logvars(dsn);
    proc sql;
        select cats("Log_",name) into :vlist separated by ' '
        from dictionary.columns
        where memname = upcase("&dsn") and TYPE="num";
    quit;
    data log_&dsn;
    set &dsn;
    array v _numeric_;
    array lv &vlist;
    do i = 1 to dim(v);
        if v{i} > 0 then lv{i} = log(v{i});
        end;
    drop i;
    run;
%mend;