BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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?
5 REPLIES 5
darrylovia
Quartz | Level 8
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
Quartz | Level 8
I tried similar way.I thought if there is something that is close to datastep way of declaring the fields.
ChrisNZ
Tourmaline | Level 20
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
Rhodochrosite | Level 12
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
Calcite | Level 5
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1630 views
  • 0 likes
  • 5 in conversation