proc sql: WHERE statement not working properly

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

proc sql: WHERE statement not working properly

[ Edited ]

I have a dataset which looks like this:

 

 

First_name	Last_name	ID_number	Subject	                        Request_type	        Course_to_add	Section_to_add	CourseCode	Section_ID
Jerry	        Harris	        1178936	        ELA Regents (High School)	Add course/section(s)	EES84H	         1	        EES43X	        1
Marty	        Brown	        8945236	        ELA Regents (High School)	Add course/section(s)	EES84H	         9	        EES43X	        9
Gina	        Frank	        1145623	        ELA Regents (High School)	Add course/section(s)	EES84H	         	        EES43X	        78

I'm trying to create a subset of this table (obs where Section_to_add = Section_ID and obs where Section_to_add != Section_ID) using the following:

 

 

 

 

PROC SQL;
CREATE TABLE invalid_section AS 
(SELECT first_name,
last_name,
id_number,
Subject, 
Request_Type, 
Course_To_Add,
Section_to_add,
CourseCode,
Section_ID
FROM course_sec
WHERE Section_to_add not = Section_ID AND Request_Type = "Add course/section(s)");
quit;

 

 

The code runs without errors but doesn't seem to be comparing the two columns as expected, as the results I get are identical to my original dataset regardless of whether course_section = section_id or not.

SAS doesn't return any errors or warnings, and the notes are all normal (i.e. Table abc created with x rows and y columns).

Both columns are the same datatype and I've tried a few variations of this (switching the order of the columns, using ne instead of not =, using = instead of not =) to no avail.

 

I'm stumped.  Any ideas?


Accepted Solutions
Solution
‎09-28-2017 04:23 PM
Esteemed Advisor
Posts: 5,479

Re: proc sql: IF statement not working properly

Posted in reply to PaigeMiller

There seems to be leading blanks in column section_to_add. Try

 

WHERE left(Section_to_add) ne left(Section_ID) AND Request_Type = "Add course/section(s)"

 

 

PG

View solution in original post


All Replies
Respected Advisor
Posts: 2,823

Re: proc sql: IF statement not working properly

Your code refers to a variable Course_Section which does not exist in your data.

--
Paige Miller
Occasional Contributor
Posts: 19

Re: proc sql: IF statement not working properly

Posted in reply to PaigeMiller

that was a typo in the post - not my actual code - it's corrected now

Respected Advisor
Posts: 2,823

Re: proc sql: IF statement not working properly

There may be (or may not be) trailing blanks, try something like this:

 

WHERE trim(Section_to_add) not = trim(Section_ID) AND trim(Request_Type) = "Add course/section(s)"
--
Paige Miller
Solution
‎09-28-2017 04:23 PM
Esteemed Advisor
Posts: 5,479

Re: proc sql: IF statement not working properly

Posted in reply to PaigeMiller

There seems to be leading blanks in column section_to_add. Try

 

WHERE left(Section_to_add) ne left(Section_ID) AND Request_Type = "Add course/section(s)"

 

 

PG
Occasional Contributor
Posts: 19

Re: proc sql: IF statement not working properly

You've cracked the case!  

 

Thanks people of the internet for, once again, solving my problems.

PROC Star
Posts: 2,316

Re: proc sql: IF statement not working properly

Well spotted.

It's much better to clean the data and remove these leading spaces in the table though.

 

 

Occasional Contributor
Posts: 19

Re: proc sql: IF statement not working properly

Posted in reply to PaigeMiller

@PaigeMiller - tried your code, still getting the same results.  I hadn't considered trailing blanks though, so still a good thing to point out.

Super User
Posts: 6,629

Re: proc sql: IF statement not working properly

The word "not" sometimes gets interpreted in funny ways.  Try switching the conditions on either side of "AND" within the WHERE clause.

Occasional Contributor
Posts: 19

Re: proc sql: IF statement not working properly

Posted in reply to Astounding

 @Astounding - tried your suggestions - got the same results.

PROC Star
Posts: 2,316

Re: proc sql: IF statement not working properly

The code you provided works fine, you must have done something else.

data HAVE;
input (FIRST_NAME 	LAST_NAME	ID_NUMBER	  REQUEST_TYPE	        COURSE_TO_ADD	SECTION_TO_ADD	COURSECODE	SECTION_ID) (:& $50.);
cards;
Jerry          Harris          1178936      Add course/section(s)   EES84H           1          EES43X          1
Marty          Brown            8945236     Add course/section(s)   EES84H           9          EES43X          9
Gina          Frank            1145623      Add course/section(s)   EES84H           2          EES43X          78
run; 
         
proc sql;      
  create table INVALID_SECTION as
  select FIRST_NAME
        ,LAST_NAME
        ,ID_NUMBER
        ,REQUEST_TYPE 
        ,COURSE_TO_ADD
        ,SECTION_TO_ADD
        ,COURSECODE
        ,SECTION_ID
  from HAVE
  where SECTION_TO_ADD ne SECTION_ID 
    and REQUEST_TYPE   eq "Add course/section(s)";
quit;
FIRST_NAME LAST_NAME ID_NUMBER REQUEST_TYPE COURSE_TO_ADD SECTION_TO_ADD COURSECODE SECTION_ID
Gina Frank 1145623 Add course/section(s) EES84H 2 EES43X 78

 

Respected Advisor
Posts: 2,823

Re: proc sql: IF statement not working properly

@ChrisNZ

Unless the data was read in differently and there are trailing blanks somewhere in the data being used by @LizGagne

--
Paige Miller
PROC Star
Posts: 2,316

Re: proc sql: IF statement not working properly

Posted in reply to PaigeMiller

@PaigeMiller Trailing spaces don't affect a string comparison. Leading spaces do.

data _null_;
  A='1 ';
  B='1                                    ';
  C=(A=B);
  putlog C=;
run; 
   

C=1

 

PROC Star
Posts: 2,316

Re: proc sql: IF statement not working properly

@LizGagne Can you change the title of this post? I don't see any IF statement here.

Occasional Contributor
Posts: 19

Re: proc sql: IF statement not working properly

@ChrisNZ done.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 680 views
  • 4 likes
  • 5 in conversation