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!
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;
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;
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;
Thanks! using first function works out in my program.
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.