SAS SQL CONTAINS or something else

[G_I_Jeff]

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!                            

[PGStats]

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

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

[G_I_Jeff]

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]

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]

@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]

EQT operator ?

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