How to obtain the last purchase per clientID

[CesarOmarHR]

I'm an EG user and i'm having trouble generating two recoded columns. The first aim is to identify the first big delay on life payments, based on that tipping point i want to identify by date the last purchase made before that event.

These are the key variables

If the delay is greater than 60 days, it is identify as the first big delay. Next, i take that payment date and identify the last purchase on SAS table 2

SAS Table 1

idclient                                                  payment date  DaysofDelay

436968 31MAR2011           70             07MAR2011         -24         

436968 31MAY2011           139.83      20JUN2011           20         

436968 15JUL2011           45.31         11SEP2011           58         

436968 30APR2011           112           30APR2011           0         

436968 15FEB2011           70             07MAR2011           20         

436968 15MAY2011           112          15MAY2011             0         

436968 30JUN2011           172.22      11SEP2011           73          First big delay

436968 15AUG2011           45.31       11SEP2011           27         

436968 28FEB2011           104.8        07MAR2011           7         

436968 30SEP2011           0.21          30SEP2011           0         

436968 15APR2011           169.14      16APR2011           1         

436968 15MAR2011           70             07MAR2011          -8         

436968 15JUN2011           145.87      11SEP2011           88         

436968 31JUL2011           45.31         11SEP2011           42         

436968 31JAN2011           70              31JAN2011           0         

437125 15JUN2011           277.52       08AUG2011           54         

437125 15JUL2011           120.54        20AUG2011           36         

437125 30APR2011           235.81       16JUN2011           47         

437125 30JUN2011           318.9         20AUG2011           51         

437125 15FEB2011           75              27FEB2011           12         

437125 15MAR2011           75             21MAR2011           6         

437125 31AUG2011           0.99          31AUG2011           0         

437125 31JAN2011           75              27FEB2011           27         

437125 31MAY2011          314.23      08AUG2011           69         

437125 28FEB2011           75             27FEB2011           -1         

437125 15MAY2011           275.01     16JUN2011           32         

437125 15APR2011           185           16APR2011           1         

437839 15MAY2011           75             17MAY2011           2         

437839 31MAY2011           75             01JUN2011           1         

437839 15APR2011           75             14APR2011           -1         

437839 30APR2011           75             02MAY2011           2         

437839 15JUN2011           98.9          07SEP2011           84         

437839 30SEP2011           0.78          30SEP2011           0         

437839 15AUG2011           26.08        07SEP2011           23         

437839 30JUN2011           113.08      07SEP2011           69         

437839 31JUL2011           26.08         07SEP2011           38         

437839 15JUL2011           26.08         07SEP2011           54         

SAS Table 1                                                                                                  

idclient   purchasedate purchase         

436968 09JAN2011      $240              

436968 31JAN2011      .                   

436968 07MAR2011     .                   

436968 22MAR2011      $425          Last purchase

436968 16APR2011      .              

436968 30APR2011      .              

436968 15MAY2011     .              

436968 20JUN2011      .              

436968 11SEP2011      .              

437125 27FEB2011      .              

437125 21MAR2011      $745         

437125 21MAR2011      .              

437125 21MAR2011      .              

437125 16APR2011      .              

437125 16JUN2011      .              

437125 08AUG2011     .              

437125 20AUG2011      .              

How do i make this work on EG?

Thanks

[TomKari]

Hi, Cesar

Could you clarify something?

For Client 436968, you also have a "big delay" a bit further down from 15JUN2011 to 11SEP2011. Do you want to use the dates to determine the first delay, or the order of the records? If it's the dates, should it be the start date or the end date that determines it? And, what to do about ties in one of the dates?

Tom

[CesarOmarHR]

I'm only interested in the first big delay according to payment dates. Ties in that particular variable are highly improbable.

I appreciate the help

Cesar

[TomKari]

Okay, here we go! It's quite a few steps, but each step is fairly simple. That's one of the big differences between Enterprise Guide and writing SAS code, it's easier to use many small steps. (I'm giving the first date in your first table the name "InvoiceDate", since you didn't name it).

Step 1
------

Create at query on your first table (with payments). The main thing you want to do here is to create a new computed column, let's call it BigDelay. In a CASE function, set it to 1 if PaymentDate - InvoiceDate > 60, else 0. Also, filter by BigDelay = 1.

This should i) figure out which records have a big delay, and ii) only keep those records.

Step 2
------

Run a rank task on the result. Use InvoiceDate as your "column to rank", and IDClient as your "Rank by" column. This will give the resulting rank 1 to the minimum (earliest) value of InvoiceDate.

Step 3
------

Run a query on this output, selecting only the records where the rank equal 1. This should give you the earliest big delay for every client.

Step 4
------

Another query. Use the result of the above step, but add your second table (I'll call it Table2) to the query. Make sure the two tables are joined on IDClient. Add a filter of "where PurchaseDate < InvoiceDate". I can't tell, but you might also need an "AND PurchaseAmount NOT IS MISSING". This will give you all of the Table2 records with a date before the cutoff date from Table 1.

Step 5
------

Run a rank task on the result. Use PurchaseDate as your "column to rank", and IDClient as your "Rank by" column. This time, in the "Options" tab, turn on "Reverse ranking...", which will rank the LATEST PurchaseDate as number 1.

Step 6
------

And finally, another query on the result, again selecting the records with rank equal to 1. This should give you a table with the record for each IDClient that meets your criteria.

I know it sounds like a lot of work, but once your familiar with these tasks it would only take about ten minutes.

Let us know how it goes,
  Tom

[CesarOmarHR]

It worked, thanks!