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.
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 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.