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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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