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?
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)"
Your code refers to a variable Course_Section which does not exist in your data.
that was a typo in the post - not my actual code - it's corrected now
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)"
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)"
You've cracked the case!
Thanks people of the internet for, once again, solving my problems.
Well spotted.
It's much better to clean the data and remove these leading spaces in the table though.
@PaigeMiller - tried your code, still getting the same results. I hadn't considered trailing blanks though, so still a good thing to point out.
The word "not" sometimes gets interpreted in funny ways. Try switching the conditions on either side of "AND" within the WHERE clause.
@Astounding - tried your suggestions - got the same results.
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 |
@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
@LizGagne Can you change the title of this post? I don't see any IF statement here.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.