BookmarkSubscribeRSS Feed
avalanche
Calcite | Level 5

Hi everyone.

I have the following list:


CustNo.       LoanType

001                 Auto
001                 Housing
002                 Housing
002                 Auto
003                 Auto
003                Housing
003                Auto
004                Housing
004                Auto
004               Housing
005               Auto
005               Auto
005              Housing
006              Housing
006              Auto
006             Auto


Expected Output:

CustNo.             AutoThenHousing? HousingThenAuto?
001                              1                               0
002                              0                               1
003                              1                               1
004                              1                               1
005                              1                               0
006                              0                               1

As you can see, I want to check the order of loan availment (i.e. auto first then housing? or housing first then auto?) Take note also that there are cases wherein a customer availed multiple loans like 003 and 004. For these customers, both categories are true.

Hope you can guide me on how to implement this in EG.Thanks.

4 REPLIES 4
TomKari
Onyx | Level 15

Are you comfortable running some code in a code window, or do you need to completely use the EG tasks to do this?

 

Tom

avalanche
Calcite | Level 5
Hi Tom. I would prefer to use EG please. Thanks.
TomKari
Onyx | Level 15

Well, I think it's possible, but it's complicated.

 

1. Using Query Builder, select everything from your table. Create a new advanced expression, and make the expression

   monotonic()

This is an unsupported function that will add the row number to the record. Let's call it SeqNo for the rest of this example.

 

2. Do the following for both streams, Auto and Housing:

2.1 Select only the records for Auto

2.2 For this table, do a summarized query, keeping CustNo, the min of SeqNo, and the max of SeqNo.

2.3 Do the same two steps for Housing.

 

3. Create a new query on the result of 2.2, and do a full outer join to the result of 2.3, joining on CustNo. This should give you a dataset with the CustNo, min and max of SeqNo for Auto, and min and max of SeqNo for Housing.

 

4. With this, create a query. Create your variables Auto_then_Housing and Housing_then_Auto as advanced expressions, where you'll use a Case function to compare the variables. For Auto_then_Housing I used this:

 

CASE

WHEN (t1.MIN_of_SeqNo_Auto < t1.MAX_of_SeqNo_Housing) & ^missing(t1.MIN_of_SeqNo_Auto)

THEN 1

ELSE 0

END

 

and for Housing_then_Auto

 

CASE

WHEN (t1.MIN_of_SeqNo_Housing < t1.MAX_of_SeqNo_Auto) & ^missing(t1.MIN_of_SeqNo_Housing)

THEN 1

ELSE 0

END

 

LinusH
Tourmaline | Level 20
Since it seems that the sort order is important to your expected results you want a variable to keep track on the original order. Like a date or an automatically incremental loan no.
If that's not available to you create it as a first step in your project by calling the monotonic() function in the Query builder.
For you inquiry itself, it's kinda row oriented problem. So SQL based Query Builder isn't fit for the task. So my guess you need a data step program solve this. It would probably involve stuff like RETAIN, BY, first./last.- logic and explicit OUTPUT. If these are not familiar to you consider to taking the free online training SAS Programming.
Data never sleeps

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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 4 replies
  • 1446 views
  • 0 likes
  • 3 in conversation