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;
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;
Hi @Lordy Can you please post the sample of the OUTPUT you want for the sample INPUT you posted ?
Did not run
Please post your results that you want that flag thing?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.