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;
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;
PS show us the code you use for transforming one variable. It will be the starting point.
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.