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;
... View more
