1

I am trying to use proc sql select into to create a variable that I then try to call later. This variable is the average price (BlockPrice).

proc sql;
   create table Block_Price_Calc as 
   select mean(Price) Into : BlockPrice
from Data1
Where As_Of_Date >= '31MAR2015'd and As_Of_Date < '07APR2015'd;
quit;

%put &BlockPrice;

proc sql;
 create table Want as 
 select *,
     (&BlockPrice) as Block
 from Data2;
quit;

The variable BlockPrice is not being recognized and it seems like it is being stored as a temporary variable. Any thoughts?

ldan
  • 41
  • 1
  • 6
  • What's the log? Does the first put works? – stallingOne Feb 04 '20 at 14:05
  • 1
    You cannot use CREATE to make a dataset and INTO to make macro variables in the same statement. Which one are you actually trying to do? Also your second query is referencing a different dataset and a different macro variable. – Tom Feb 04 '20 at 14:06
  • The first put statement creates a temporary variable, but the calculation is correct. I want to store the mean of Data1 and create a new column in Data2 with that calculated mean. The error in the second proc sql is: ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. – ldan Feb 04 '20 at 14:10

1 Answers1

1

An INTO clause cannot be used in a CREATE TABLE statement.

proc sql; 
   select mean(Price) Into : BlockPrice
   from Data1
   Where As_Of_Date >= '31MAR2015'd and As_Of_Date < '07APR2015'd;
quit;
Llex
  • 1,770
  • 1
  • 12
  • 27