Hello I have the following dataset but I am really stuck on how to do the hierarchy for few weeks actually. Any suggestions would be GREATLY appreciated!! Thank you!
Here are the rules:
If the group contains > 20 on any instance or then it should be code as 'TOO HIGH' or if it is between 16-20 TWICE in a row
If the group is between 11 through 15 twice in a row, then it should be 'HIGH'
If the group is between 6 through 10 twice in a row, then it should be 'Low'
If the group is between 1 through 5 twice in a row, then it should be 'Normal'
Basically if the two consequentive events don't happen by top hierarchy (too high), you skip to the next level (high, low, normal, etc)
Here is the dataset
ID | value | seq_id |
1 | 3 | 1 |
1 | 1 | 2 |
1 | 2 | 3 |
1 | 4 | 4 |
1 | 1 | 5 |
2 | 20 | 1 |
2 | 21 | 2 |
2 | 5 | 3 |
3 | 1 | 1 |
3 | 6 | 2 |
3 | 7 | 3 |
3 | 1 | 4 |
3 | 4 | 5 |
3 | 21 | 6 |
3 | 17 | 7 |
3 | 1 | 8 |
3 | 2 | 9 |
4 | 6 | 1 |
4 | 9 | 2 |
4 | 12 | 3 |
4 | 13 | 4 |
5 | 1 | 1 |
5 | 6 | 2 |
5 | 7 | 3 |
5 | 14 | 4 |
5 | 20 | 5 |
6 | 11 | 1 |
6 | 12 | 2 |
6 | 25 | 3 |
6 | 16 | 4 |
6 | 21 | 5 |
Here's the output I am looking for:
ID | value | seq_id | output |
1 | 3 | 1 | Normal |
1 | 1 | 2 | |
1 | 2 | 3 | |
1 | 4 | 4 | |
1 | 1 | 5 | |
2 | 20 | 1 | Too High |
2 | 21 | 2 | |
2 | 5 | 3 | |
3 | 1 | 1 | |
3 | 6 | 2 | |
3 | 7 | 3 | |
3 | 1 | 4 | |
3 | 4 | 5 | |
3 | 21 | 6 | Too High |
3 | 17 | 7 | |
3 | 1 | 8 | |
3 | 2 | 9 | |
4 | 6 | 1 | |
4 | 9 | 2 | |
4 | 12 | 3 | High |
4 | 13 | 4 | |
5 | 1 | 1 | |
5 | 6 | 2 | Low |
5 | 7 | 3 | |
5 | 14 | 4 | |
5 | 19 | 5 | |
6 | 11 | 1 | |
6 | 12 | 2 | |
6 | 25 | 3 | Too High |
6 | 16 | 4 | |
6 | 21 | 5 |
|
What is the reason that you want to put the info on the first value of the 2?
If there are multiple rulestrue for the same ID, you only want the largest (too high-high-low-normal)?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.