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.
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;
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;
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 |
Your code is OK. You only need to widen the date columns in your viewing window to see the formatted dates.
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.