I would recommend Chris's solution, but "gild the lilly" a little more.
Useful in more than just proc sql, what is needed here is a loop that will generate the pattern
where "something else" is constant and n is a counter
This might generate a string useful in many places
[pre]%macro gen1( n, pattern= something###else, from=1, by=1 ) ;
%local i ;
%do i= &from %to &n %by &by ;
%sysfunc( tranwrd( %str(&pattern), ###, &i ))
%mend gen1 ;[/pre]
Use that like
%put %gen1(24, pattern=###) ;
proc sql ;
select trx1, %gen1( 24, PATTERN=%str( , trx###), from=2 ) )
beware that second demo is untested
we need to be careful how to make sure that the pattern is "data" to the macro rather than resolve looking like syntax to the macro - so I used %str()
other times I might use %superq()
other times, I've found it useful to apply a format like Z3. to the &i
the attention is on the macro language handling strings, rather than on the sas syntax for a data step or proc.
no matter how much I review, allways it seems to need a corrction
You use the following idea to get the names and then the list of variables:
ARRAY D(*) D1-D24;
DO I = 1 TO 24 BY 1;
D(I) = I;
STOP; RETURN; RUN;
CREATE TABLE NAMES AS
WHERE UPCASE(LIBNAME) EQ 'WORK'
AND UPCASE(SUBSTR(NAME,1,1)) EQ 'D'
SELECT DISTINCT NAME
INTO :NAMEVAR SEPARATED BY ', '
The macro variable NAMEVAR contains the list of variable that you need.