BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nstdt
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

8 REPLIES 8
Astounding
PROC Star

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
Quartz | Level 8
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
PROC Star

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
Quartz | Level 8

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
Super User

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
Barite | Level 11
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
Quartz | Level 8

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1610 views
  • 12 likes
  • 4 in conversation