There is exam table data

-origin data

I want to delete rows of specific key including between '2002' and '2006' using code of SAS proc sql.

-result data

 '''error code'''
 PROC SQL;
 CREATE TABLE a.not_2002_2006 as
 SELECT t.STU_ID, 
        t.STU_KEY, 
        t.STU_CODE
 FROM a.stu as t
 WHERE t.STU_KEY NOT LIKE '2002%' 
 and t.STU_KEY NOT LIKE '2003%' 
 and t.STU_KEY NOT LIKE '2004%' 
 and t.STU_KEY NOT LIKE '2005%' 
 and t.STU_KEY NOT LIKE '2006%'
 GROUP BY t.STU_ID;
 QUIT;  

Let me know how to solve this problem.

CodePudding user response：

You are probably getting the LIKE operator requires character operands error. If that is the case, this is due to the fact that your STU_KEY is a numeric column. Tweak the where statement to use the like operator with a character column using the

CodePudding user response：

You could try with SUBSTR: https://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212267.htm

PROC SQL;
 CREATE TABLE a.not_2002_2006 as
 SELECT t.STU_ID, 
        t.STU_KEY, 
        t.STU_CODE
 FROM a.stu as t
 WHERE  substr(t.STU_KEY,1,4) >=2007 
 GROUP BY t.STU_ID;
 QUIT;  

CodePudding user response：

In case STU_KEY is a number, treat it as a number and define your conditions as numbers.

To get the records between 2002000 and 2006999:

WHERE
  STU_KEY >= 2002000 AND STU_KEY < 2007000 /* 2002000-2006999 */

To get the records which are not in the above range

WHERE
  STU_KEY < 2002000 OR STU_KEY >= 2007000

To get the ones greater than 2007000

WHERE
  STU_KEY >= 2007000 /* 2007000  */