Hi Guys,
I have a input file like below:
customer# start_date end_date
123abd 20140830 20150120
456efg 20130101 20140421
The history file has all the transaction for that customer with below layout
customer# process_date transaction_id transaction_amt
For each customer#, I need to lookup in history file and extract transaction where process_date between start_date and end_date.
can anyone help me with suggestion how I can accomplish this task. Should I use PROC SQL?
Best Regards,
Santosh Guduru.
Not sure if you're looking to return history transaction data fields or fields from the input file data, but this would be something that you may want using SQL subquery. Haven't tested it though:
proc sql;
create table want as
select a.*
from history_transaction_data as a
having a.customer# in (select distinct customer#
from input_file_data as b
where a.process_date between b.start_date and b.end_date);
quit;
Hope this helps or gives you an idea!
What do you want the output to look like. Do you want all the transactions which appear between those dates? You could do (assumes dates are numeric date format):
proc sql;
create table WANT as
select A.*,
B.TRANSACTION_ID,
B.TRANSACTION_AMT
from HAVE A
left join HISTORY B
on A.CUSTOMER=B.CUSTOMER
and B.PROCESS_DATE between A.START_DATE and A.END_DATE;
quit;
Here you go:
Data have1;
infile cards dsd;
input customer$ start_date end_date;
cards;
123abd,20140830,20150120
456efg,20130101,20140421
;
data have2;
infile cards dsd;
input customer$ process_date;
cards;
123abd,20140831
123abd,20140829
456efg,20130102
456efg,20120101
;
proc sql;
create table want as
select distinct
a.customer,
a.start_date,
b.process_date,
a.end_date
from have1 a inner join
have2 b on
a.customer = b.customer and
a.start_date < b.process_date < a.end_date;
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.