I'm trying to use Call Execute along with Proc expand in order to log transform a large number of variables.
I'm however getting some errors and would appreciate any advice.
Here is my code set-up:
1)
%let orderedvars2 = a b c d /*long list of variables to be log transformed */;
2)
I have a macro called get_word which extracts the i-th element of the list of variables in orderedvars2
%macro get_word(string=,j=);
%let word_cnt=%sysfunc(countw(%superq(string)));
%let var&j=%scan(&string,&j);
&&var&j.
%mend ;
3)
Then I have Call Execute with proc expand in the argument as below. I thought I was supposed to concatenate the output of %get_word along with the usual syntax for Proc expand. I would like to rename the original variables with the prefix 'log_' in front. My code produces the following error:
NOTE 137-205: Line generated by the invoked macro "TEST".
35 call execute ( 'convert '|| %get_word(&orderedvars2,&i)||' = log_'||%get_word(&orderedvars2,&i)||' /
__
22
35 ! transformout=(log);');
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, INPUT, PUT.
ERROR: More positional parameters found than defined.
Here is my macro %test below:
%macro test;
%let n = %sysfunc(countw(&orderedvars2));
data _null_;
call execute ('proc expand data=work.Standardized3 out=logvars1 method = none; id date2; ');
%do i = 1 %to &n;
call execute ( 'convert '|| %get_word(&orderedvars2,&i)||' = log_'||%get_word(&orderedvars2,&i)||' / transformout=(log);');
%end;
call execute ( ';run;');
run;
%mend;
%test;
Thank you for any help and advice.
Why not run it from a dataset without macro code?
data orderedvars;
input var $;
cards;
a
b
c
d
;
run;
data _null_;
set orderedvars end=done;
if _n_ = 1 then
call execute ('proc expand data=work.Standardized3 out=logvars1 method = none; id date2; ');
call execute ( 'convert '|| trim(var) ||' = log_'|| trim(var) ||' / transformout=(log);');
if done then
call execute ( ';run;');
run;
It looks like you're pretty close here. The interior macro calls should add double quotes:
|| %get_word(&orderedvars2,&i)||
should become:
|| "%get_word(&orderedvars2,&i)" ||
It's the difference between CALL EXECUTE seeing || a || vs. || "a" ||.
Alternatively, add the double quotes within the macro definition, changing &&var&j. to "&&var&j."
Since you defined the macro with keyword parameters, you have to call it with keyword parameters:
%get_word(string=&orderedvars2, j=&i)
Better might be to simplify and get rid of the macro entirely. Within CALL EXECUTE:
|| "%scan(&orderedvar2, &i) " ||
I also tried with single quotes instead of double quotes. and get these errors--- not sure what they indicate-- here it looks like the %get_word macro's output does not get generated inside call execute:
Why not run it from a dataset without macro code?
data orderedvars;
input var $;
cards;
a
b
c
d
;
run;
data _null_;
set orderedvars end=done;
if _n_ = 1 then
call execute ('proc expand data=work.Standardized3 out=logvars1 method = none; id date2; ');
call execute ( 'convert '|| trim(var) ||' = log_'|| trim(var) ||' / transformout=(log);');
if done then
call execute ( ';run;');
run;
How to stop at compilation on on a specific iteration
data _null_;
if _n_=0 then do;
%let rc=%sysfunc(dosubl('
proc sql noprint;
select
quote(var)
into
:vars
separated by
","
from
orderedvars
;quit;
'));
end;
* compile fail?;
%sysfunc(ifc(&rc.=0,%str( ),%str(put "failed at compilation";stop;)));
do var=(&vars);
call symputx('var',var_;
rc=dosubl('
proc expand data=work.Standardized3 out=logvars1 method = none;
id date2;
convert &var. = log_&var. transformout=(log);
run;quit;
');
* excecution fail;
if rc>0 then do;
put "Failed for variable &var";
put "Processing stopped";
stop;
end;
end;
run;quit;
Thanks, I really like the solution and it works.
The only thing is that the variable names are actually long and sometimes are just long alphanumeric codes -- in this case , is there a short cut to creating the dataset orderedvars? I have all the variable names stored as a list in the macrovariabe &orderedvars2.
I tried using the code below but it didn't run. Should I use call execute inside the data step --- what would be the syntax, if its advisable?
data orderedvars;
input var $;
cards;
&orderedvars2;
;
run;
It says in the error message:
NOTE: Line generated by the macro variable "ORDEREDVARS2".
27 STD_fri STD_VEHICLE_SALES STD_PASSCAR_SALES STD_TRK_SALES STD_CPI_TRANSPORT STD_LF_PART_RATE STD_GASCPI_NSA STD_CANCPI
_______
180
27 ! STD_CANCORECPI STD_UNEMPLOY_RATE STD_BOND_RATE_10YR STD_CPI STD_CPI_CORE STD_RGDP STD_RGDP_USD STD_EXCH_CAND_USD
27 ! STD_EXCH_CAND_EUR
ERROR 180-322: Statement is not valid or it is used out of proper order.
Create the dataset in this way:
data orderedvars;
length var $32;
do i = 1 to countw("&orderedvars");
var = scan("&orderedvars",i);
output;
end;
keep var;
run;
Now you can inspect the dataset for being correct and then run the data step with call execute off it.
You can integrate the steps, but I just want my call execute data steps to be as simple as can be.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.