Is there "NEXT/PRIOR" logic in SAS EG?

[Proulx06]

Our group is moving from Hyperion Intelligence Designer (aka Brio) to SAS EG 4.3. We are in the process of rebuilding some of our queries, and have run into a roadblock.

We often bring back transactional (cash) data, and use Brio's "next" or "prior" functions to isolate the first payment date. For example, given this dataset:

Date - Customer - Transaction Type

1/1/12 - Customer1 - Cash Payment

2/1/12 - Customer1 - Cash Payment

3/1/12 - Customer1 - Cash Payment

We could create a calculated column that would say "if customer=(prior(customer) then 0 else 1", and what that would do is isolate that 1/1/12 payment with a "1" that we could then use in the dataset to isolate all first payments for each customer. I'd like to find similar functionality in SAS EG 4.3, but not having any luck. Does such functionality exist?

Thanks!

[art297]

Are you looking for EG point/click from menu options or would including program code suffice.  If it can be the latter, you might want to look at using a by variable and the first. variable that is automatically created.  Also, take a look at:

http://www.sascommunity.org/wiki/Look-Ahead_and_Look-Back

[Proulx06]

Hi Arthur,

Thanks for the reply. Being new to SAS EG, a point/click menu option would be great, but I'm not against experimenting with code if that's the only solution. I'll take a look at your link.

Thanks.      

[sasspan · Accepted solution]

No problem Proulx!

You need to use the "Rank" Node from the "Data" Tasks. Make sure that you have a unique data set by customer id, transactions data.  So use a query and just select customer ID, transaction date, and check the box at the bottom that says "Select distinct rows only".  Now you have a data set with each customer and each date they had a transactions.  Highlight this data set then click the Rank node.  Under the rank node insure that you sort by ascending, drag transaction date to "Columns to rank", drag customerID to "Rank by"  Go to "Option" on the left pane, insure that ranking method is smallest to largest.  Now this out put will give a series of integers next to the dates (****IF YOU DON'T HAVE INTEGERS YOU DID NOT SELECT DISTINCT ROWS IN THE PREVIOUS STEP****).  From here make a query off this and SELECT customerID and transaction date, go to FILTER and create a basic filter for Rank=1.  This gives you the first date a customer had a transaction.  If you need the last date a customer had a transaction....repeat the above steps only, sort by descending in the rank, and under options, still insure that :ranking method is "smallest to largest" and in the same options check the box that says "Reverse ranking to be from largest to smallest"  Now you will filter on rank =1 again only you will have the last date.

Cheers,

Spaner

[sasspan]

Also if you want to know the payment amount associated with these first/last dates, Join the query/filter back to the orignal data set with the payment information, make sure you join on customerID AND transaction date.  If someone had multiple transactions on there first/last date, you would then SUM this field, and you're done.

[Proulx06]

Thank you thank you Sasspan! That is a perfect solution and allows us to retain much of our functionality. Best part is it will be incredible easy to implement across our group as it's not difficult. Thank you!

[sasspan]

Glad I could help!