topic Re: proc sql from dictionary.columns where column names begin with "CDD_"? in SAS Procedures

proc sql from dictionary.columns where column names begin with "CDD_"?

gzr2mz39 — Mon, 06 May 2019 20:22:01 GMT

Hi,

How would I modify this code to only select column names that begin with "CDD_"?
proc sql;
select name into :charlist separated by ' '
from dictionary.columns
where UPCASE(LIBNAME)="WORK" and upcase(memname)="ECM";
quit;

Thank you.

Re: proc sql from dictionary.columns where column names begin with "CDD_"?

Reeza — Mon, 06 May 2019 20:24:56 GMT

ADD: and name like 'CDD_%'

Re: proc sql from dictionary.columns where column names begin with "CDD_"?

PeterClemmensen — Mon, 06 May 2019 20:26:24 GMT

add

and substr(name, 1, 4)="CDD_"

to your where clause

Re: proc sql from dictionary.columns where column names begin with "CDD_"?

ChrisHemedinger — Mon, 06 May 2019 20:27:45 GMT

AND, you can skip the UPCASE for libname and memname (because they are always upper case), but you do need it for the variable name:

proc sql;
select name into :charlist separated by ' '
from dictionary.columns
where libname="SASHELP" and memname="CARS" 
 and upcase(name) like "M%";
quit;

 Column Name
 --------------------
 Make                
 Model               
 MSRP                
 MPG_City            
 MPG_Highway

Re: proc sql from dictionary.columns where column names begin with "CDD_"?

ccaulkins9 — Tue, 07 May 2019 21:20:45 GMT

@Chris_Hemedinger,
'ja ever notice how in NotePad++ the sql Language overlay jives real well with SAS? There's another but since most of the work I do with SAS is more than likely SQL-related (proc_sql_is_your_friend, where are you now? B|)