topic Re: How to pull records before and after based on an event? in SAS Enterprise Guide

How to pull records before and after based on an event?

mikefiorelli — Tue, 25 Sep 2018 18:19:04 GMT

hi so i am using SAS ENTERPRISE GUIDE. I am trying to pull claims data for my customers based on an event. the event is the purchase of a car. so i am trying to pull all customers who purchases a car in 2017 and pull all their purchases that happened 2 months before and after the original purchase of the car. i want to exclude anything that happened over 2 months pre and post.

table 1 pictured here is the original table that has all my customers, table 2 is the final product that i need to get to. i also like to create a column in my table 2 saying what was the original purchase (in this case it's the car)

any help is appreciated.

can I attach my project? i won't let me do that

Re: How to pull records before and after based on an event?

Astounding — Tue, 25 Sep 2018 18:52:52 GMT

Are all your date variables legitimate SAS dates?

You want to base the data on the purchase date. But your data doesn't contain a purchase date. Can you clarify that?

If a purchase was made on February 28, what is the the two-month cutoff? April 28? April 30? Would a 60-day window be preferable?

If a customer purchased two cars a month apart, all the purchases in between should be extracted. (And more than that as well.) But for the purchases that fall into the window between the two car purchases, should they appear once in the final data set or twice? How will you determine which other purchases are associated with which car purchase?

Re: How to pull records before and after based on an event?

Reeza — Tue, 25 Sep 2018 19:25:17 GMT

You need to do what's called a SQL self join here, at least that's the easiest method. You can do it via the GUI but it's a bit of a pain. Are you looking for a coding solution or a GUI solution?

Can you provide sample data via text - especially if you want code - otherwise it's easier to just tell you the approach.

Instructions on generating data is here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

If you can't provide your real data, make some fake data that looks close enough and has a few different scenarios, ie only before, only after, multiple purchases within the two month period, multiple vehicles within the two month period and no purchases before and after.

@mikefiorelli wrote:

hi so i am using SAS ENTERPRISE GUIDE. I am trying to pull claims data for my customers based on an event. the event is the purchase of a car. so i am trying to pull all customers who purchases a car in 2017 and pull all their purchases that happened 2 months before and after the original purchase of the car. i want to exclude anything that happened over 2 months pre and post.

table 1 pictured here is the original table that has all my customers, table 2 is the final product that i need to get to. i also like to create a column in my table 2 saying what was the original purchase (in this case it's the car)

any help is appreciated.

can I attach my project? i won't let me do that

Re: How to pull records before and after based on an event?

mikefiorelli — Wed, 26 Sep 2018 01:22:40 GMT

Service start date would be my purchase date. 60 days pre and 60 days post are fair enough. i appreciate the help

Re: How to pull records before and after based on an event?

mikefiorelli — Wed, 26 Sep 2018 01:40:08 GMT

this is my sample data. the table has million of customers.my service start date for the car is the main date that I want to use to capture all claims 2 months pre and 2 months post. 60 days pre and post is fair enough.

customer service start date service end date type of purchase
A 1/1/2017 1/5/2017 Car
A 11/21/2016 12/1/2016 Truck
A 1/8/2017 1/23/2017 Plane
A 6/4/2016 6/6/2016 boat
B 1/6/2017 1/9/2017 Car
B 11/11/2016 12/4/2016 Truck

I created this process below where i imported my table first, i created a query(querybuilder1) to pull the car purchases, than query 2 pull all claims except the car. query 3 combine both tables(i did a join on customer )and filtered within the date that falls 2 months pre and post.i used that function on the service start date from my table 2: -60 < t1.'service start date'n - t2.'service start date'n < 60. the final result(query3) combine both tables but it didn't stack the results, it added them next to each other(not what i wanted, see second picture)

Re: How to pull records before and after based on an event?

Reeza — Wed, 26 Sep 2018 03:02:04 GMT

For appending records, try using a UNION within Query Builder (not sure if that's possible) or use an APPEND procedure. Note that the names must be the same to 'stack' your data.