Help with look up customer data in transaction history file.

[gskn4u]

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. 

[dcruik]

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!

[RW9]

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;

[Steelers_In_DC]

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;