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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.