BookmarkSubscribeRSS Feed
G_I_Jeff
Obsidian | Level 7

I have an obersvation that is 48 characters long. I would like to group the data based off the first 8 characters using the CASE function as follows:

 

PROC SQL;                                          
  SELECT DSNAME,                                   
         SPACE1,                                   
         SPACE2,                                   
         SPACE3,                                   
         SPACE4,                                   
         SPACE5,                                   
         SPACE6,                                   
         CASE DSNAME                               
          WHEN CONTAINS 'SYS%' OR 'K%' THEN 'SYSTEM'
          WHEN CONTAINS 'ASL%' THEN 'RD'           
          WHEN CONTAINS 'FS%' THEN 'FS'            
          ELSE 'UNKNOWN'                           
         END AS OWNER                              
   FROM INPT.DATASETS;                             
QUIT;                  

 

My results; every observation has an OWNER of SYSTEM. I would have assumed that not narrowing to the first 8 characters of DSNAME would have given me an even broader range of values for OWNER, but no.

 

Can someone throw me a bone?

Thanks in advance!                            

5 REPLIES 5
PGStats
Opal | Level 21

CASE WHEN DSNAME LIKE 'SYS%' OR DSNAME LIKE 'K%' THEN 'SYSTEM' ... etc.

Look up the syntax of the CASE expression in the documentation.

PG
G_I_Jeff
Obsidian | Level 7

I was using the LIKE condition, but it produced the same results. I also need to narrow the search to the first 8 characters of DSNAME.

Haikuo
Onyx | Level 15

Failed to see where 'first 8 character' kicks in, as there is no position nor length condition being addressed in your code, but error is due to you have a always true condition:

WHEN CONTAINS 'SYS%' OR 'K%' THEN 'SYSTEM'

try to change it to:

 

CASE WHEN (DSNAME CONTAINS 'SYS%') OR (DSNAME CONTAINS 'K%') THEN 'SYSTEM'

the older way 'K%' will always be valued as 1 in boolean, as it does not evalue whether it contains, it evalues whether 'K%' is null, and it isn't.

 

FreelanceReinh
Jade | Level 19

@G_I_Jeff: Also, please note that LIKE and CONTAINS cannot be used interchangeably. In particular, the percent sign is a wildcard character ("matches any sequence of zero or more characters") in LIKE conditions, whereas in CONTAINS conditions it is just an ordinary character (literal %). That is:

DSNAME LIKE 'SYS%'

is true for values of DSNAME such as 'SYS', 'SYSTEM', 'SYS123', ... (but not for 'System', 'systemic', 'UNSYSTEMATIC', ...), whereas

DSNAME CONTAINS 'SYS%'

would be true for values such as 'SYS%', 'SYS%TEM', 'UNSYS%TEMATIC', ... (but not for 'SYS', 'SYSTEM', 'UNSYSTEMATIC', 'unsys%tematic', ...).

Ksharp
Super User

EQT operator ?

CASE                                 
WHEN DSNAME EQT 'SYS' OR DSNAME EQT  'K' THEN 'SYSTEM'
... etc.

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
  • 5 replies
  • 10719 views
  • 2 likes
  • 5 in conversation