08-31-2017 02:09 PM
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
08-31-2017 02:19 PM
The answer is NO.
08-31-2017 04:34 PM
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.