topic Re: How to flag same addresses occurring on the same date in SAS Programming

How to flag same addresses occurring on the same date

Lordy — Fri, 29 Mar 2019 18:49:28 GMT

I have a data that looks like the table below. I want to flag the same addresses occurring on the same date as an event.

This is the code I used but SAS is treating address as numeric and character at the same time. Any help will be appreciated.

Store ID	Address	Date	Customer Name
1	T6H 4M5	1-Jan-19	John
1	T6H 4M5	1-Jan-19	Mary
1	T6H 1R1	3-Mar-18	Ami
1	T6H 3M1	3-Mar-18	Emi
1	T6H 1A2	2-Feb-17	Dan
1	T6H 1A2	2-Feb-18	Trish
1	T6K 1G2	1-Jan-19	Ivy
1	T6H 1N2	4-Jan-19	Dex
1	T6H 1N2	3-Dec-18	May
1	T6H 4M5	2-Mar-17	Nate
:	:	:	:
1	T6H 4M5	5-Mar-19	Hans

proc sort data=have;
by address date;
run;

data want;

do until (last.date);

set have;

by address date;

if address then has_address=1;

if date then has_date=1;

end;

flag=0;

do until (last.date);

set want;

by address and date;

if has_address and has_date the output;

end;

drop has_:

run;

Re: How to flag same addresses occurring on the same date

novinosrin — Fri, 29 Mar 2019 19:04:50 GMT

Hi @Lordy Can you please post the sample of the OUTPUT you want for the sample INPUT you posted ?

Re: How to flag same addresses occurring on the same date

Lordy — Fri, 29 Mar 2019 19:03:25 GMT

Did not run

Re: How to flag same addresses occurring on the same date

novinosrin — Fri, 29 Mar 2019 19:05:21 GMT

Please post your results that you want that flag thing?

Re: How to flag same addresses occurring on the same date

Lordy — Fri, 29 Mar 2019 19:07:31 GMT

flag=1 for all addresses that has the same date
flag=0 otherwise

Re: How to flag same addresses occurring on the same date

Lordy — Fri, 29 Mar 2019 19:09:07 GMT

First two rows will have flag=1

Re: How to flag same addresses occurring on the same date

novinosrin — Fri, 29 Mar 2019 19:13:50 GMT


data have;
infile cards expandtabs truncover;
input StoreID	Address & $10.	Date :date8.	CustomerName $;
format date date9.;
cards;
1	T6H 4M5	 1-Jan-19	John
1	T6H 4M5	 1-Jan-19	Mary
1	T6H 1R1	 3-Mar-18	Ami
1	T6H 3M1	 3-Mar-18	Emi
1	T6H 1A2	 2-Feb-17	Dan
1	T6H 1A2	 2-Feb-18	Trish
1	T6K 1G2  1-Jan-19	Ivy
1	T6H 1N2	 4-Jan-19	Dex
1	T6H 1N2	 3-Dec-18	May
1	T6H 4M5	 2-Mar-17	Nate
1	T6H 4M5	 5-Mar-19	Hans
;


proc sql;
create table want(drop=m) as
select *,count(*)>1 as flag,monotonic() as m
from have
group by StoreID,Address,date
order by m;
quit;

Re: How to flag same addresses occurring on the same date

Lordy — Fri, 29 Mar 2019 19:26:53 GMT

Worked as magic 🙂
Thank you