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.
Are you comfortable running some code in a code window, or do you need to completely use the EG tasks to do this?
Tom
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
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.