topic Re: Help with look up customer data in transaction history file. in SAS Procedures

Help with look up customer data in transaction history file.

gskn4u — Wed, 29 Jul 2015 14:24:29 GMT

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.

Re: Help with look up customer data in transaction history file.

dcruik — Wed, 29 Jul 2015 14:41:34 GMT

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!

Re: Help with look up customer data in transaction history file.

RW9 — Wed, 29 Jul 2015 14:55:52 GMT

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;

Re: Help with look up customer data in transaction history file.

Steelers_In_DC — Wed, 29 Jul 2015 15:00:13 GMT

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;