USING :INTO in SUBQUERIES

[User12]

Hi Guys,

I have a quick question can we create macro variables using :INTO clause in subqueries

select distinct names into :names1-:names&total from
(select names,Count(distinct names) into :total  from sample)

can we use :into in  a subquery as shown above

[novinosrin]

The answer is NO.

RTM-->

The INTO clause for the SELECT statement can assign the result of a calculation or the value of a data column (variable) to a macro variable. If the macro variable does not exist, INTO creates it. You can check the PROC SQL macro variable SQLOBS to see the number of rows (observations) produced by a SELECT statement.

The INTO clause can be used only in the outer query of a SELECT statement and not in a subquery. The INTO clause cannot be used when you are creating a table (CREATE TABLE) or a view (CREATE VIEW).

Macro variables created with INTO follow the scoping rules for the %LET statement. 

Values assigned by the INTO clause use the BEST8. format.

[ballardw]

As @novinosrin says, no.

But the "fix" is insted of subquery create a separate query that generates just the macro variables you attempt from the subquery. But that may not be needed at all.

proc sql noprint;
   select distinct names into :names1-:names9999
   from sample
   ;
quit;

If you do not 9999 distinct names on the names1-namesXXX are created.

The automatic SAS macro variable &sqlobs will have the count of returned items in the last previous query.

So

%let NameCount = &sqlobs;

immediately after the Proc SQL will capture that count.

If you suspect you need more than 9999 macro variables (a real bad sign IMHO) increase the count.