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!
CASE
WHEN DSNAME LIKE 'SYS%' OR DSNAME LIKE 'K%' THEN 'SYSTEM'
... etc.
Look up the syntax of the CASE expression in the documentation.
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.
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.
@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', ...).
EQT operator ?
CASE
WHEN DSNAME EQT 'SYS' OR DSNAME EQT 'K' THEN 'SYSTEM'
... etc.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.