Create Proc Expand in macro using call execute

[nstdt]

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.

[Astounding]

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."

[nstdt]

Thanks so much for your response. I tried both suggestions and still get
errors... please do let me know if you have any idea what may be causing
the problem!

1.) Replacing &&var&j. with "&&var&j." inside the macro %get_word but not
using double-quotes in the macro call gives the error:

NOTE 137-205: Line generated by the invoked macro "TEST".
3 call execute ( 'convert '|| %get_word(&orderedvars2,&i)||' =
--
22
3 ! log_'||%get_word(&orderedvars2,&i)||' / 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.



2.) Enclosing the macro call in double_quotes, but not changing the
%get_word macro also gives an error:
%macro test;
%let n = %sysfunc(countw(&orderedvars2));
data _null_;
call execute ('proc expand data=STNDStandardized3 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;

ERROR: More positional parameters found than defined.


##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Astounding]

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) " ||

[nstdt]

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:

%macro test;

  %let n = %sysfunc(countw(&orderedvars2));

  data _null_;

  call execute ('proc expand data=STNDStandardized3 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;

ERROR: More positional parameters found than defined.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: DATA statement used (Total process time):

      real time           0.08 seconds

      cpu time            0.09 seconds

NOTE: CALL EXECUTE generated line.

1   + proc expand data=STNDStandardized3 out=logvars1 method = none;  id date2;

NOTE: Line generated by the CALL EXECUTE routine.

2   + convert = log_ / transformout=(log);

      -------

      180

[Kurt_Bremser · Accepted solution]

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;

[rogerjdeangelis]

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;

[nstdt]

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.

[Kurt_Bremser]

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.