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
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
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
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
It worked, thanks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.