transforming large number of variables with sas macro

Reply
New User
Posts: 1

transforming large number of variables with sas macro

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;

Super User
Posts: 10,259

Re: transforming large number of variables with sas macro

[ Edited ]
Posted in reply to cvan-hull

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;


 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,259

Re: transforming large number of variables with sas macro

Posted in reply to cvan-hull

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 6,778

Re: transforming large number of variables with sas macro

[ Edited ]
Posted in reply to cvan-hull

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;

Super User
Posts: 13,556

Re: transforming large number of variables with sas macro

Posted in reply to cvan-hull

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.

Esteemed Advisor
Posts: 5,532

Re: transforming large number of variables with sas macro

[ Edited ]
Posted in reply to cvan-hull

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;


PG
Ask a Question
Discussion stats
  • 5 replies
  • 108 views
  • 0 likes
  • 5 in conversation