BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kjohnsonm
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26
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

3 REPLIES 3
Astounding
PROC Star

Couldn't you use:

 

where column_name = upcase(column_name)

 

Am I missing something?

RW9
Diamond | Level 26 RW9
Diamond | Level 26
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.

kjohnsonm
Lapis Lazuli | Level 10

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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