topic Re: USING :INTO in SUBQUERIES in SAS Programming

USING :INTO in SUBQUERIES

User12 — Thu, 31 Aug 2017 18:09:42 GMT

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

Re: USING :INTO in SUBQUERIES

novinosrin — Thu, 31 Aug 2017 18:19:23 GMT

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.

Re: USING :INTO in SUBQUERIES

ballardw — Thu, 31 Aug 2017 20:34:12 GMT

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.

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