Thank you Chris for commenting -- they help me to understand the whole thing here. Really appreciated.
1. It is very expensive as shown by the the note in the log. Sorting and remerging of the data may be needed.
I don't see any big problems with remerging info in the log. Can you advise what do you mean by expensive ? (they sound like problematic )
2. Function monotonic(0 is not production is should not be used in production jobs, as mentioned.
I understand monotonic() is not documented and thank you for restate and suggest that it should not be used in production.
This is just my exploration in replacing data steps as we might need shift current work and using DBT in the future.
3. The record selected by the HAVING clause can vary from one run to the next as SQL can process data in any order.
I thought as long as the input data is properly sorted, we would expect to get the data as wanted.
I have tested different order combination of the source data, it looks like it produces what is targeted.
4. For all these reasons, and as @Kurt_Bremser mentioned, you should use the right tool, which here is the data step.
No problems at all, I am using data step in most cases. As mentioned earlier, it is just an exploration in preparation moving to DBT.
After all, would love to hear any suggestions, ideas, experience, that any one would love to share in migrating current SAS code to DBT ...
... View more