How to remove specific rows ina dataset

[Sandeep77]

Hi Experts,

I have created a dataset but have some dates in the address line 1. I want to remove those rows with address but not sure how to do it. I have figured out how to find all the rows with dates in the address line but not sure how to remove it from the dataset. I am not sure how to write a code to remove these rows from the dataset. Please help.

proc sql;
create table NOA as
select *
from trace_nonlit
where Debt_Code not in (select debt_code from sasuser.trace_noa_flag);
quit;
/* Data pulls the dates in address */
proc sql;
create table NOA_Date_in_Add as
select *
from Strategy
where
      upcase('Traced_ad_1'n ) like '%-JAN %' 
or    upcase('Traced_ad_1'n ) like '%-FEB %'
or    upcase('Traced_ad_1'n ) like '%-MAR %'
or    upcase('Traced_ad_1'n ) like '%-APR %'
or    upcase('Traced_ad_1'n ) like '%-MAY %'
or    upcase('Traced_ad_1'n ) like '%-JUN %'
or    upcase('Traced_ad_1'n ) like '%-JUL %'
or    upcase('Traced_ad_1'n ) like '%-AUG %'
or    upcase('Traced_ad_1'n ) like '%-SEP %'
or    upcase('Traced_ad_1'n ) like '%-OCT %'
or    upcase('Traced_ad_1'n ) like '%-NOV %'
or    upcase('Traced_ad_1'n ) like '%-DEC %';
quit;

[Tom]

Your code looks reasonable.  Explain how it did not work. 

Did it not run?  Show the log with the code and notes and errors.

Did it run but not exclude everything you want it to exclude? Then show some example values of the variable TRACED_AD_1 that made it through to NOA_Date_in_Add  that you wanted to eliminate.

[Sandeep77]

Hi Tom,

The code works fine. It shows all the address which have date in address line 1. But I want to write a code which removes these rows which includes date in address. Here is the examples that I get when I run the code which gives me details of any dates available in address line 1 but I want these to remove from the dataset.

debt_code	Traced_ad_1
2543684	     15-Apr Clearburn Road
8463246	     10-Mar North Junction Street
2546434	     15-Apr Clearburn Road

[PaigeMiller · Accepted solution]

You asked for 

where
      upcase('Traced_ad_1'n ) like '%-JAN %' 
or    upcase('Traced_ad_1'n ) like '%-FEB %'
or    upcase('Traced_ad_1'n ) like '%-MAR %'
or    upcase('Traced_ad_1'n ) like '%-APR %'
or    upcase('Traced_ad_1'n ) like '%-MAY %'
or    upcase('Traced_ad_1'n ) like '%-JUN %'
or    upcase('Traced_ad_1'n ) like '%-JUL %'
or    upcase('Traced_ad_1'n ) like '%-AUG %'
or    upcase('Traced_ad_1'n ) like '%-SEP %'
or    upcase('Traced_ad_1'n ) like '%-OCT %'
or    upcase('Traced_ad_1'n ) like '%-NOV %'
or    upcase('Traced_ad_1'n ) like '%-DEC %'

but you want the opposite?

Then how about this:

where not (
      upcase('Traced_ad_1'n ) like '%-JAN %' 
or    upcase('Traced_ad_1'n ) like '%-FEB %'
or    upcase('Traced_ad_1'n ) like '%-MAR %'
or    upcase('Traced_ad_1'n ) like '%-APR %'
or    upcase('Traced_ad_1'n ) like '%-MAY %'
or    upcase('Traced_ad_1'n ) like '%-JUN %'
or    upcase('Traced_ad_1'n ) like '%-JUL %'
or    upcase('Traced_ad_1'n ) like '%-AUG %'
or    upcase('Traced_ad_1'n ) like '%-SEP %'
or    upcase('Traced_ad_1'n ) like '%-OCT %'
or    upcase('Traced_ad_1'n ) like '%-NOV %'
or    upcase('Traced_ad_1'n ) like '%-DEC %')

[Sandeep77]

Thank you, that was exactly I was looking for. Much appreciated.