SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
cvan-hull
Calcite | Level 5

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;

5 REPLIES 5
Kurt_Bremser
Super User

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;


 

Astounding
PROC Star

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;

ballardw
Super User

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.

PGStats
Opal | Level 21

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1076 views
  • 0 likes
  • 5 in conversation