I want to create a new column called “Priority” and in order to do so I have to look at multiple TABLE_ID’s and within them are ACCOUNT_IDs that are assigned to the TABLE_ID. There can be duplicate TABLE_IDs but unique ACCOUNT_ID. Multiple ACCOUNT_IDs can be assigned to one TABLE_ID which is why I want to prioritize them. The issue I am having is that instead of prioritizing them I’m getting numbers beyond what I have assigned as row_number when I partition.

Here is an example of the table that I have:

Here is the expected results:

I basically want to prioritize the ACCOUNT_ID based on the ACCOUNT_NAME where:

PREMIUM = 1
PERFORMANCE = 2
VALUE = 3 
PLUS = 4 

However, if a TABLE_ID doesn’t have an ACCOUNT_ID with a PREMIUM ACCOUNT_NAME then the priority should be assigned to the next which is PERFORMANCE which should be Priority = 1 as it’s the first priority account when PREMIUM is absent. I am trying to achieve this using proc sql in sas and I keep getting priority numbers greater than what is set out in the case conditions.

SELECT 
    *, 
    ROW_NUMBER() OVER (
        PARTITION BY TABLE_ID
        ORDER BY 
            CASE 
                WHEN ACCOUNT_NAME = 'PREMIUM' THEN 1 
                WHEN ACCOUNT_NAME  = 'PERFORMANCE' THEN 2
                WHEN ACCOUNT_NAME  = 'VALUE' THEN 3
                WHEN ACCOUNT_NAME  = 'PLUS' THEN 4
                ELSE 5
            END
    ) AS Priority
FROM 
   TABLE_A as a;