topic PROC SQL SELECT * where {all obs that are entirley in caps no matter the word}? in SAS Programming

PROC SQL SELECT * where {all obs that are entirley in caps no matter the word}?

kjohnsonm — Wed, 25 Apr 2018 15:11:47 GMT

Hello All,
I have a need to select all records no matter what the contents might be if the word is entirely in caps?

Does anyone have a good approach on this? I was looking at 'compress' then select not null and am failing to get it. Maybe I just have not come up with the right mix in the 'compress' command, or maybe it will not work (if I get mixed case)
PS I am searching metadata and all keys to the tables I care about have been added in all caps all non keys* have been entered in lower case is why I care... [*or should have been 😎 ]

Have:

table_name column_name
xw_school EXT_ORG_ID
xw_school atp_cd
xw_school eff_status
xw_school Ext_Org_City*
xw_school ext_org_country
xw_school ext_org_country_descr
xw_school ext_org_county
xw_school ext_org_descr
xw_school ext_org_descr50
xw_school EXT_org_descrshort*
xw_term ACAD_CAREER
xw_term STRM
xw_term acad_year
xw_term aid_year
xw_term fiscal_year
xw_term full_acad_year
xw_term session_code
xw_term term_30th_dt
xw_term term_begin_dt
xw_term term_census_dt
xw_term term_code
xw_term term_descr
xw_term term_descr15

want:
xw_school EXT_ORG_ID
xw_term ACAD_CAREER
xw_term STRM

Re: PROC SQL SELECT * where {all obs that are entirley in caps no matter the word}?

Astounding — Wed, 25 Apr 2018 15:18:11 GMT

Couldn't you use:

where column_name = upcase(column_name)

Am I missing something?

Re: PROC SQL SELECT * where {all obs that are entirley in caps no matter the word}?

RW9 — Wed, 25 Apr 2018 15:16:27 GMT

select * from have where lengthn(compress(column_name,"","u")) =0;

This will compress out all uppercase characters, and thus if the length = 0 there is only upper case.

Re: PROC SQL SELECT * where {all obs that are entirley in caps no matter the word}?

kjohnsonm — Wed, 25 Apr 2018 15:33:42 GMT

A variation on what you have:

proc sql;
select
TABLE_NAME
,COLUMN_NAME
,compress(COLUMN_NAME,,"KUP") as keep_upper

from my_metadata
where
compress(COLUMN_NAME,"","KU") is not null and COLUMN_NAME = compress(COLUMN_NAME,"","KU")
;
quit;

/* K for keep, U upper case, P punctuation keeps the _ life is golden */

thank you.