- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
PS show us the code you use for transforming one variable. It will be the starting point.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;