topic Re: PROC SQL select vars with prefix in SAS Programming

PROC SQL select vars with prefix

markc — Mon, 15 Apr 2019 02:39:09 GMT

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;

Re: PROC SQL select vars with prefix

novinosrin — Mon, 15 Apr 2019 02:49:31 GMT

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;

Re: PROC SQL select vars with prefix

Astounding — Mon, 15 Apr 2019 02:51:48 GMT

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;

Re: PROC SQL select vars with prefix

PGStats — Mon, 15 Apr 2019 02:52:35 GMT

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;