topic SAS SQL CONTAINS or something else in SAS Programming

SAS SQL CONTAINS or something else

G_I_Jeff — Tue, 12 Apr 2016 18:23:26 GMT

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!

Re: SAS SQL CONTAINS or something else

PGStats — Tue, 12 Apr 2016 18:53:53 GMT


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

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

Re: SAS SQL CONTAINS or something else

G_I_Jeff — Tue, 12 Apr 2016 18:59:25 GMT

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.

Re: SAS SQL CONTAINS or something else

Haikuo — Tue, 12 Apr 2016 19:00:02 GMT

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.

Re: SAS SQL CONTAINS or something else

FreelanceReinh — Tue, 12 Apr 2016 19:40:34 GMT

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

Re: SAS SQL CONTAINS or something else

Ksharp — Wed, 13 Apr 2016 01:19:43 GMT

EQT operator ?

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