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;
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 %')
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.
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
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 %')
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.