Dear All:
I'm new to macro. Need some help to write my first macro. I hope to deflate 100 variables in the table by a variable. I wrote the following:
May I ask how to code such that the variable names will be automatically create according to a convention. For example, I know different deflator. The variable name is always original_viariable_name / deflator_name. Thank you!
%let vars = var1 var2 var3 ;
data want;
set have;
&vars._deflator = &vars / deflator;
run;
Hope to get var1_deflator, var2_deflator, and var3_deflator, etc....
Thanks !!!
I think you are confusing macros and arrays / do loops.
To do what you what you can use:
data want;
drop i;
set have;
array n(3) var1 var2 var3;
array result(3) var1_deflator var2_deflator var3_deflator;
do i = 1 to 3;
result(i) = n(i) / deflator ;
end;
run;
Depending on how your variables are named you might be able to streamline the coding of the 100 variables.
EJ
I think you are confusing macros and arrays / do loops.
To do what you what you can use:
data want;
drop i;
set have;
array n(3) var1 var2 var3;
array result(3) var1_deflator var2_deflator var3_deflator;
do i = 1 to 3;
result(i) = n(i) / deflator ;
end;
run;
Depending on how your variables are named you might be able to streamline the coding of the 100 variables.
EJ
Thank you so much! It works -
The code works, but I have to manually name each of the variables to be created.
May I ask how to code such that the variable names will be automatically create according to a convention. For example, I know different deflator. The variable name is always original_viariable_name / deflator_name. Thank you!
Then you need some tweak on Eric's code:
proc sql;
select cats(name,'_deflator') into :newvar separated by ' ' from dictionary.columns
where libname='WORK' AND MEMNAME='HAVE'; /*HAVE TO BE ALL CAPPED*/
select count(name) into :ct trimmed from dictionary.columns
where libname='WORK' AND MEMNAME='HAVE'; /*HAVE TO BE ALL CAPPED*/
QUIT;
data want;
drop i;
set have;
array n(&ct.) var1--var3;/*from the first var to the last var, order matters and don't skip*/
array result(&ct.) &newvar.;
do i = 1 to &ct.;
result(i) = n(i) / deflator ;
end;
run;
You do not need to run the query twice to count the number of values return. PROC SQL already puts that value into the automatic macro variable SQLOBS.
Thanks, Tom. Why I always forget about it.:smileysilly:
Haikuo
Hi, Hai:
I tried your awesome code. It is reporting the following. I think the reason is on my table, there are a few identifier variable (character variables) that cannot be divided. My previous writing is kind of confusing. All hope to achieve is that for SOME of the numeric variables in the table, I hope to divide them by a common variable called deflator. The new variables' naming convention is OLD_VARIABLE_NAME__deflator. The calculation would be like OLD_VARIABLE__deflator = OLD_VARIABLE / deflator ;
I could easily do this for one or two but when it is approaching hundreds, it is just too....
24 data r.funda2;
25 drop i;
26 set r.funda1;
27 array n(&ct.) gvkey--fundaid;/*from the first var to the last var,
27 ! order matters and don't skip*/
NOTE: The array n has the same name as a SAS-supplied or user-defined
function. Parentheses following this name are treated as array
references and not function references.
SYMBOLGEN: Macro variable CT resolves to 20
ERROR: fundaid does not follow gvkey on the list of previously defined
variables.
ERROR: Too few variables defined for the dimension(s) specified for the array
n.
28 array result(&ct.) &newvar.;
SYMBOLGEN: Macro variable CT resolves to 20
SYMBOLGEN: Macro variable NEWVAR resolves to gvkey_AT datadate_AT fyear_AT
FYR_AT AT_AT CAPX_AT CEQ_AT CSHO_AT DVC_AT LT_AT NI_AT OANCF_AT
SALE_AT XAD_AT XRD_AT SICH_AT prcc_f_AT endfyr_AT begfyr_AT
funda_id_AT
Looks like your real world is less friendly than the scenario you presented, which happens all the time .
I would try following:
1) rename the first array 'n' to something else, like '_n'. N() denotes a SAS internal function, so it may confuses SAS, as being reflected in the log.
2) Make sure all of the your array variables are numeric in this case.
3)Define 'SOME', are they adjacent to each other? are there other rules? maybe we can figure out a way to automate it.
Haikuo
Thank you !!!
Build the list of new and old variable names at the same time and then use two parallel arrays.
proc sql;
select name
, cats(name,'_deflator')
into :oldvars separated by ' '
, :newvars separated by ' '
from dictionary.columns
where libname='WORK'
and MEMNAME='HAVE'
and upcase(name) ne 'DEFLATOR'
and type = 'num'
;
quit;
data want;
set have;
array _old &oldvars ;
array _new &newvars ;
do over _old ;
_new = _old / deflator ;
end;
run;
OH YEAH!!!!! IT WORKS! I'm so happy. Thank you so much, guys! You are fabulous!
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.