topic Re: proc sql: IF statement not working properly in SAS Programming

proc sql: WHERE statement not working properly

LizGagne — Thu, 28 Sep 2017 20:51:09 GMT

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?

Re: proc sql: IF statement not working properly

PaigeMiller — Thu, 28 Sep 2017 20:02:25 GMT

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

Re: proc sql: IF statement not working properly

LizGagne — Thu, 28 Sep 2017 20:03:48 GMT

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

Re: proc sql: IF statement not working properly

PaigeMiller — Thu, 28 Sep 2017 20:08:05 GMT

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)"

Re: proc sql: IF statement not working properly

Astounding — Thu, 28 Sep 2017 20:10:05 GMT

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

Re: proc sql: IF statement not working properly

ChrisNZ — Thu, 28 Sep 2017 20:14:58 GMT

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

Re: proc sql: IF statement not working properly

PaigeMiller — Thu, 28 Sep 2017 20:17:28 GMT

@ChrisNZ

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

Re: proc sql: IF statement not working properly

PGStats — Thu, 28 Sep 2017 20:20:42 GMT

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)"

Re: proc sql: IF statement not working properly

LizGagne — Thu, 28 Sep 2017 20:21:10 GMT

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

Re: proc sql: IF statement not working properly

ChrisNZ — Thu, 28 Sep 2017 20:21:28 GMT

@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

Re: proc sql: IF statement not working properly

LizGagne — Thu, 28 Sep 2017 20:22:09 GMT

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

Re: proc sql: IF statement not working properly

LizGagne — Thu, 28 Sep 2017 20:24:38 GMT

You've cracked the case!

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

Re: proc sql: IF statement not working properly

ChrisNZ — Thu, 28 Sep 2017 20:25:39 GMT

Well spotted.

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

Re: proc sql: IF statement not working properly

ChrisNZ — Thu, 28 Sep 2017 20:30:24 GMT

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

Re: proc sql: IF statement not working properly

LizGagne — Thu, 28 Sep 2017 20:51:32 GMT

@ChrisNZ done.