How to delete specific key including between '2002' and '2006'?

user_14058090 0
Viewed 82

Here is the exam data table that I would like to process here:

-origin data

STU_ID STU_KEY STU_CODE
123 2002123 A120
124 2002124 A120
125 2002125 A120
126 2002126 A120
127 2002127 A120
128 2003123 A120
129 2003124 A120
130 2004123 A120
131 2005123 A120
132 2006123 A120
133 2007123 A120
134 2008321 A120

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

-result data

STU_ID STU_KEY STU_CODE
133 2007123 A120
134 2008321 A120 
 '''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.

sas sql
4 Answers

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 PUT function.

proc sql;
    create table want as select stu_id, stu_key, stu_code 
    from have 
    where put(stu_key, 7.) like '2007%' 
        or put(stu_key, 7.) like '2008%'
    group by stu_id;
quit;

Output:

enter image description here

user_104686130
user_164619520

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+ */
user_34693910

Some easy ways,

If character variable

  • STU_KEY between "2002" and "2007"
  • substr(STU_KEY,1,4) between "2002" and "2006"

If numeric variable

  • int(STU_KEY/1000) between 2002 and 2006
user_12499620
Related