topic Re: how to use "not like" operator to work with a list in SAS Procedures

how to use "not like" operator to work with a list

xliu1 — Thu, 19 Nov 2020 03:39:42 GMT

I try to use "not like" operator in case when statement in proc sql,

PROC SQL;
CREATE TABLE COURSES AS
SELECT ID,
               CASE WHEN GRADE_AWARDED LIKE 'D%' OR GRADE_AWARDED LIKE 'F%' THEN 'DF Grades'
	                WHEN GRADE_AWARDED LIKE 'W%' THEN 'W Grades'
			        WHEN GRADE_AWARDED NOT (LIKE 'D%' or LIKE 'F%' OR LIKE 'W%') THEN 'non-DFW'
			        ELSE 'No Grade' END AS Grade_Type
FROM COURSES_TAKEN;
QUIT;

but I am getting error message: any idea how should I modify this code? Thanks!

Re: how to use "not like" operator to work with a list

PeterClemmensen — Thu, 19 Nov 2020 07:16:31 GMT

The Like Operator handles one expresion one time. Therefore, this is the correct syntax

PROC SQL;
CREATE TABLE COURSES AS
SELECT ID,
               CASE WHEN GRADE_AWARDED LIKE 'D%' OR GRADE_AWARDED LIKE 'F%' THEN 'DF Grades'
	                 WHEN GRADE_AWARDED LIKE 'W%' THEN 'W Grades'
			           WHEN  GRADE_AWARDED NOT LIKE 'D%' 
                       or GRADE_AWARDED NOT LIKE 'F%' 
                       or GRADE_AWARDED NOT LIKE 'W%') THEN 'non-DFW'
			      ELSE 'No Grade' END AS Grade_Type
FROM COURSES_TAKEN;
QUIT;

Re: how to use "not like" operator to work with a list

andreas_lds — Thu, 19 Nov 2020 07:35:25 GMT

Maybe using the first function makes the last when statement easier:

when first(Grade_Awarded) not in ('D', 'F', 'W') then 'non-DFW'

Moving the logic into a format definition sounds like a good idea (code is untested)

proc format;
  value $GradeType
    'D', 'F' = 'DF Grades'
	'W' = 'W Grades'
	other = 'non-DFW'
  ;
run;

data courses;
  set courses_taken;
  
  length Grade_Type $ 10;
  
  Grade_Type = put(first(Grade_Awarded), $GradeType.);
run;

Re: how to use "not like" operator to work with a list

xliu1 — Thu, 19 Nov 2020 14:17:06 GMT

Thanks! using first function works out in my program.