topic Re: How to remove specific rows ina dataset in New SAS User

How to remove specific rows ina dataset

Sandeep77 — Wed, 22 Jun 2022 16:35:59 GMT

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;

Re: How to remove specific rows ina dataset

Tom — Wed, 22 Jun 2022 16:49:26 GMT

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.

Re: How to remove specific rows in a dataset

Sandeep77 — Wed, 22 Jun 2022 17:03:03 GMT

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

Re: How to remove specific rows in a dataset

PaigeMiller — Wed, 22 Jun 2022 17:26:31 GMT

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 %')

Re: How to remove specific rows in a dataset

Sandeep77 — Wed, 22 Jun 2022 17:47:54 GMT

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