Proc Sql Select Into Is Creating a Temporary Variable that I can't Call

Question

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?

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

Alexey Sigida Alexey Sigida · Accepted Answer · 2020-02-04T14:09:23

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;

Proc Sql Select Into Is Creating a Temporary Variable that I can't Call

1 Answers