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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

14 REPLIES 14
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
LizGagne
Obsidian | Level 7

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

PaigeMiller
Diamond | Level 26

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
PGStats
Opal | Level 21

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
LizGagne
Obsidian | Level 7

You've cracked the case!  

 

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

ChrisNZ
Tourmaline | Level 20

Well spotted.

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

 

 

LizGagne
Obsidian | Level 7

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

Astounding
PROC Star

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

LizGagne
Obsidian | Level 7

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

ChrisNZ
Tourmaline | Level 20

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
Diamond | Level 26

@ChrisNZ

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

--
Paige Miller
ChrisNZ
Tourmaline | Level 20

@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

 

ChrisNZ
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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