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
PROC Star

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
Super User

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
PROC Star

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 2024

Innovate_SAS_Blue.png

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. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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