DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 127
Accepted Solution

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

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 8) ]

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


Accepted Solutions
Solution
‎04-25-2018 11:26 AM
Super User
Super User
Posts: 9,227

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

Posted in reply to kjohnsonm
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.

View solution in original post


All Replies
Super User
Posts: 6,543

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

[ Edited ]
Posted in reply to kjohnsonm

Couldn't you use:

 

where column_name = upcase(column_name)

 

Am I missing something?

Solution
‎04-25-2018 11:26 AM
Super User
Super User
Posts: 9,227

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

Posted in reply to kjohnsonm
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.

Frequent Contributor
Posts: 127

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

[ Edited ]

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 96 views
  • 1 like
  • 3 in conversation