Deduping on Min and Max date Using Proc SQL

[eaherbst]

Hi All,

I am trying to join two tables together, the first has a list of accounts(a) and the second has a list of associated merchants(b). As each account may be associated with the same merchant more than once, I would like to return a final table with distinct account numbers based on the min of the first date and max of last date fields in the associated merchant table.

Have for a single account:

a.ID_Key,       a.EntryDate                a.AccountNumber, 

   123              03JAN19:00:00:00        5678         

 b.Merch_ID_Key     b.Entry_Date                 b.First_Merch_Date             b.Last_Merch_Date

    123                        03JAN19:00:00:00        21OCT18:00:00:00              22DEC18:00:00:00

    123                        03JAN19:00:00:00        01MAR16:00:00:00              20SEP17:00:00:00

Want:

a.ID_Key,       a.EntryDate                a.AccountNumber,    b.First_Merch_Date             b.Last_Merch_Date

   123              03JAN19:00:00:00        5678                        01MAR16:00:00:00              22DEC18:00:00:00

My output is giving me the correct amount of rows but the dates are coming back as "*******"

Here is my query:

proc sql;
create table MerchAcct as
select a.ID_Key, a.EntryDate, a.AccountNumber, 
min(b.First_Merch_Date) as First_Merch_Date format date9.,
max(b.Last_Merch_Date) as Last_Merch_Date format date9.
from Accounts a
join Merch b
on a.ID_Key=b.ID_Key
and a.EntryDate=b.EntryDate
group by a.ID_Key, a.EntryDate, a. AccountNumber;
quit;

As a reference I am using SAS EG.

[AMSAS · Accepted solution]

Hi  eaherbst

I'm not sure how you are viewing the results when you say "My output is giving me the correct amount of rows but the dates are coming back as "*******""

What I did notice in your code is the input dataset have SAS Datetime values (e.g. 22DEC18 00:00:00) and in your join you format the First_Merch_Date & Last_Merch_Date with SAS Date formats. Which may be the issue.

You have a couple of options available to you depending on what you want the output to look like

1) Use the datepart function to extract the SAS date value from the SAS datetime value:
min(datepart(b.First_Merch_Date)) as First_Merch_Date format date9.,

2) Format the values with a datetime format:
min(b.First_Merch_Date) as First_Merch_Date format datetime.,

Here's a test I ran:

data accounts ;
	id_key="123";
	entrydate="03JAN2019 00:00:00"dt ;
	accountNumber="5678" ;
run ;
data merch;
	id_key="123";
	accountNumber="5678" ;
	entrydate="03JAN2019 00:00:00"dt ;
	first_merch_date="21OCT2018 00:00:00"dt ;
	last_merch_date="21OCT2018 00:00:00"dt ;
	output ;
	id_key="123";
	accountNumber="5678" ;
	entrydate="03JAN2019 00:00:00"dt ;
	first_merch_date="01MAR2016 00:00:00"dt ;
	last_merch_date="20SEP2017 00:00:00"dt ;
	output ;

run ;


proc sql;
create table MerchAcct1 as
select a.ID_Key, a.EntryDate, a.AccountNumber, 
min(datepart(b.First_Merch_Date)) as First_Merch_Date format date9.,
max(datepart(b.Last_Merch_Date)) as Last_Merch_Date format date9.
from Accounts a
join Merch b
on a.ID_Key=b.ID_Key
and a.EntryDate=b.EntryDate
group by a.ID_Key, a.EntryDate, a. AccountNumber;
quit;

proc sql;
create table MerchAcct2 as
select a.ID_Key, a.EntryDate, a.AccountNumber, 
min(b.First_Merch_Date) as First_Merch_Date format datetime.,
max(b.Last_Merch_Date) as Last_Merch_Date format datetime.
from Accounts a
join Merch b
on a.ID_Key=b.ID_Key
and a.EntryDate=b.EntryDate
group by a.ID_Key, a.EntryDate, a. AccountNumber;
quit;

[novinosrin]

Hi @eaherbst   Your code works when i tested

data a;
input ID_Key      Entry_Date  :datetime20.           AccountNumber;
format Entry_Date datetime20. ;
cards;
123              03JAN19:00:00:00        5678
;

data b;
input ID_Key    ( Entry_Date               First_Merch_Date           Last_Merch_Date) ( :datetime20.);
format Entry_Date               First_Merch_Date           Last_Merch_Date datetime20.;
cards;
123                        03JAN19:00:00:00        21OCT18:00:00:00              22DEC18:00:00:00
123                        03JAN19:00:00:00        01MAR16:00:00:00              20SEP17:00:00:00
;


proc sql;
create table MerchAcct as
select a.ID_Key, a.Entry_Date, a.AccountNumber, 
min(b.First_Merch_Date) as First_Merch_Date format=datetime20. ,
max(b.Last_Merch_Date) as Last_Merch_Date format=datetime20.
from a a
join b b
on a.ID_Key=b.ID_Key
and a.Entry_Date=b.Entry_Date
group by a.ID_Key, a.Entry_Date, a. AccountNumber;
quit;

The SAS System

ID_Key	Entry_Date	AccountNumber	First_Merch_Date	Last_Merch_Date
123	03JAN2019:00:00:00	5678	01MAR2016:00:00:00	22DEC2018:00:00:00

[PGStats]

Your code is OK. You only need to widen the date columns in your viewing window to see the formatted dates.