BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
xliu1
Quartz | Level 8

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!

xliu1_0-1605757068968.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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;

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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;
andreas_lds
Jade | Level 19

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;
xliu1
Quartz | Level 8

Thanks! using first function works out in my program.

sas-innovate-white.png

Missed SAS Innovate in Orlando?

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.

 

Register now

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 17678 views
  • 2 likes
  • 3 in conversation