Create Proc Expand in macro using call execute

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

Create Proc Expand in macro using call execute

[ Edited ]

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.


Accepted Solutions
Solution
‎04-07-2017 01:14 AM
Super User
Posts: 7,852

Re: Create Proc Expand in macro using 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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 5,516

Re: Create Proc Expand in macro using call execute

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

Contributor
Posts: 41

Re: Create Proc Expand in macro using call execute

Posted in reply to Astounding
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. -##
Super User
Posts: 5,516

Re: Create Proc Expand in macro using call execute

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

Contributor
Posts: 41

Re: Create Proc Expand in macro using call execute

Posted in reply to Astounding

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
Solution
‎04-07-2017 01:14 AM
Super User
Posts: 7,852

Re: Create Proc Expand in macro using 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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 505

Re: Create Proc Expand in macro using call execute

Posted in reply to KurtBremser
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;
Contributor
Posts: 41

Re: Create Proc Expand in macro using call execute

Posted in reply to KurtBremser

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.

 

Super User
Posts: 7,852

Re: Create Proc Expand in macro using call execute

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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