Column selection in proc sql

[SASPhile]

if we have 24 months of data in 24 columns like trx1 trx2 ..trx24 there is a way which is convenient in datastep to select those columns like trx1--trx24.

in proc sql we have to type each column name in the statement like
a.trx1,a.trx2 ..a.trx24.

is there a way to avoid manual typing and have an easy way to select columns?

[darrylovia]

SASPhile

Not sure if this is easy but it gets the job done and can be made for flexible too.


%macro test;
data one;

array trx(24);

do i=1 to 1000;
do j=1 to 24;
trx=ranuni(1);
end;
output;
end;
run;

proc sql;
select trx1 %do i=2 %to 24;
,trx&i
%end;
from one;
%mend test;
%test;

[SASPhile]

I tried similar way.I thought if there is something that is close to datastep way of declaring the fields.

[ChrisNZ]

you also do

proc sql;
select * from udv(keep=trx1--trx24);
quit;



otherwise, the macro can be limited to:


proc sql;
select trx1 %macro loop; %do i=2 %to 24; ,trx&i %end;%mend;%loop from one;
quit;

[Peter_C]

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
something{n}else
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 ))
%end ;
%mend gen1 ;[/pre]
Use that like
%put %gen1(24, pattern=###) ;
OR
proc sql ;
select trx1, %gen1( 24, PATTERN=%str( , trx###), from=2 ) )
from txn.dataset
;
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


Message was edited by: Peter.C

[MikeM]

You use the following idea to get the names and then the list of variables:
DATA TEST;
DROP I;
ARRAY D(*) D1-D24;
DO I = 1 TO 24 BY 1;
D(I) = I;
END;
OUTPUT;
STOP; RETURN; RUN;
PROC SQL;
CREATE TABLE NAMES AS
SELECT NAME
FROM DICTIONARY.COLUMN
WHERE UPCASE(LIBNAME) EQ 'WORK'
AND UPCASE(MEMNAME)='TEST'
AND UPCASE(TYPE)='NUM'
AND UPCASE(SUBSTR(NAME,1,1)) EQ 'D'
;
SELECT DISTINCT NAME
INTO :NAMEVAR SEPARATED BY ', '
FROM WORK.NAMES
;
QUIT;
%PUT NAMEVAR=&NAMEVAR;

The macro variable NAMEVAR contains the list of variable that you need.

PROC SQL;
SELECT &NAMEVAR