topic Re: Why the code fails inside macro but ok not in macro?! in SAS Programming

Why the code fails inside macro but ok not in macro?!

hellohere — Tue, 04 Jun 2024 10:19:58 GMT

The code is straightforward, to get the dif from two variables. The dataset has the two variables, which are both
of numeric. 

But the one inside marco complains. The "same" code works ok without macro.

Anyone?!



MPRINT(DOLOOP):   proc sql ;
MPRINT(DOLOOP):   select avg(IF)-avg(dlastprice) into: lpadj from hs300_0604 ;
ERROR: The AVG summary function requires a numeric argument.
MPRINT(DOLOOP):   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

59956  proc sql;
59957  select avg(IF)-avg(dlastprice)  from hs300_0604 ;
59958  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

Re: Why the code fails inside macro but ok not in macro?!

hellohere — Tue, 04 Jun 2024 10:28:26 GMT

Anyway/Macro to tell which one variable at a dataset is numeric or not?!

So can use ahead of the SQL to avoid complains?!

Re: Why the code fails inside macro but ok not in macro?!

Patrick — Tue, 04 Jun 2024 12:24:32 GMT

Works for me. Verify that both of your variables are of type numeric.

data hs300_0604;
  if=2; dlastprice=1;output;
  if=4; dlastprice=3;output;
run;

proc sql noprint;
  select avg(IF)-avg(dlastprice) into :lpadj 
  from hs300_0604
  ;
quit;
%put &=lpadj;

38         %put &=lpadj;
LPADJ=       1

Update: As a reaction on what @yabwon posted (worth testing) the code also works for me within a macro.

data hs300_0604;
  if=2; dlastprice=1;output;
  if=4; dlastprice=3;output;
run;

%macro doit(param1,param2);
  proc sql noprint;
    select avg(&param1)-avg(&param2) into :lpadj 
    from hs300_0604
    ;
  quit;
  %put &=lpadj;
%mend;

options mprint;
%doit(if,dlastprice);

MPRINT(DOIT):   proc sql noprint;
MPRINT(DOIT):   select avg(if)-avg(dlastprice) into :lpadj from hs300_0604 ;
MPRINT(DOIT):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

LPADJ=       1

Re: Why the code fails inside macro but ok not in macro?!

yabwon — Tue, 04 Jun 2024 12:01:40 GMT

inside your macro code make it:

select %unquote(avg(&whatEverMacrovariabeYouHaveHere.)-%unquote(avg(&whatOtherMacrovariabeYouHaveThere.))
into ...

Bart

Re: Why the code fails inside macro but ok not in macro?!

yabwon — Tue, 04 Jun 2024 12:03:21 GMT

And if I was right, read this article by Susan O'Connor: https://stats.oarc.ucla.edu/wp-content/uploads/2016/02/bt185.pdf

Re: Why the code fails inside macro but ok not in macro?!

Tom — Tue, 04 Jun 2024 15:08:57 GMT

Hard to tell since you did not provide the macro.