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

[hellohere]

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

[hellohere]

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

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

[Patrick]

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

[yabwon]

inside your macro code make it:

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

Bart

[yabwon]

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

[Tom]

Hard to tell since you did not provide the macro.