topic Re: Column selection in proc sql in SAS Programming

Column selection in proc sql

SASPhile — Mon, 16 Aug 2010 20:29:27 GMT

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?

Re: Column selection in proc sql

darrylovia — Tue, 17 Aug 2010 12:36:24 GMT

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;

Re: Column selection in proc sql

SASPhile — Tue, 17 Aug 2010 16:48:59 GMT

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

Re: Column selection in proc sql

ChrisNZ — Wed, 18 Aug 2010 01:06:48 GMT

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;

Re: Column selection in proc sql

Peter_C — Wed, 18 Aug 2010 06:44:30 GMT

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

Re: Column selection in proc sql

MikeM — Wed, 18 Aug 2010 18:53:04 GMT

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