BookmarkSubscribeRSS Feed
gskn4u
Obsidian | Level 7

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. 

3 REPLIES 3
dcruik
Lapis Lazuli | Level 10

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 904 views
  • 0 likes
  • 4 in conversation