DATA Step, Macro, Functions and more

SAS SQL CONTAINS or something else

Reply
Frequent Contributor
Posts: 75

SAS SQL CONTAINS or something else

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!                            

Respected Advisor
Posts: 4,919

Re: SAS SQL CONTAINS or something else


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

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

PG
Frequent Contributor
Posts: 75

Re: SAS SQL CONTAINS or something else

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.

Respected Advisor
Posts: 3,156

Re: SAS SQL CONTAINS or something else

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.

 

Trusted Advisor
Posts: 1,117

Re: SAS SQL CONTAINS or something else

@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', ...).

Super User
Posts: 10,020

Re: SAS SQL CONTAINS or something else

EQT operator ?

CASE                                 
WHEN DSNAME EQT 'SYS' OR DSNAME EQT  'K' THEN 'SYSTEM'
... etc.
Ask a Question
Discussion stats
  • 5 replies
  • 422 views
  • 2 likes
  • 5 in conversation