DATA Step, Macro, Functions and more

USING :INTO in SUBQUERIES

Reply
Contributor
Posts: 27

USING :INTO in SUBQUERIES

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

PROC Star
Posts: 283

Re: USING :INTO in SUBQUERIES

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.
Super User
Posts: 11,343

Re: USING :INTO in SUBQUERIES

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.

Ask a Question
Discussion stats
  • 2 replies
  • 119 views
  • 2 likes
  • 3 in conversation