PROC SQL select vars with prefix

[markc]

Hello,

How can I use a PROC SQL to select only variables from a SAS table which start with a prefix?

For example:

PROC SQL;

create table1 as

select a.prefix:,

from table2 as a;

QUIT;

[novinosrin]

Hi @markc 

I would be surprised if somebody like PGstats might come up with something as I don't think Proc sql supports variable lists. This means you would have to query dictionary tables and load the varlist in macro separated by ',' and call in a select clause.

Ok that being said you could use keep= dataset option like-->

data w;
array t(10)(10*35);
j=57665;
run;

proc sql;
create table want as
select *
from w(keep=t:);
quit;

[Astounding]

You have to spell out each variable when using SQL.  There are ways to get macro language to save you some of the typing, but macro language would have to generate a program that lists each variable.

Of course, if you switch to a DATA step, the coding is easy:

data want;
   set have (keep=prefix: ) ;
run;

[PGStats]

You could use sashelp.vcolumns :

proc sql noprint;
select name into :cols separated by ","
from sashelp.vcolumn 
where libname="SASHELP" and upcase(memname)="HEART" and
    prxmatch("/^s/i", name) ;
create table subset as
select &cols. 
from sashelp.heart;
quit;