How to fill column using values in 2 other columns in PROC SQL / SAS code in SAS Enterprise Guide?

Question

I have table in SAS Enterprise Guide like below:

Data types:

• VAL1 - character

• ID - numeric

• VAL2 - character (with only 3 possible values: P, C, S)

  ID	EVENT	GRADE
  123	NY	P
  123	NY	P
  123	NY	S
  55	MAD	C
  55	MAD	S
  55	LON	S

• ID - column with ID

• EVENT - event name

• GRADE - evaluation of the event (mark)

And I need to create table where will be ID, EVENT and GRADE of event, but:

• grade P or C are more important that grade S, so if some pair ID - EVENT has P or C then this pair ID - EVENT has P or C
• when some pair ID-EVENT has only grade S (without P or C) then stay S

So as a result I need something like below:

ID  |EVENT|GRADE
----|-----|----
123 | NY  | P  --> because P is more important than S
55  | MAD | C  --> because C is more important than S
55  | LON | S  --> because has only S

How can I do that in normal SAS code or in PROC SQL in Enterprise Guide ? :)

Answer 1

Here is a SQL-based solution that will work assuming C and P only occur once per id & event. We'll assign values to C, P, and S:

C, P = 1
S    = 2

If we take the minimum of those values per (id, event), we will be left with a unique list of IDs and events in a hierarchical order.

proc sql;
    create table want(drop=value) as
        select distinct 
            id
          , event
          , grade
          , CASE when grade IN('C', 'P') then 1 else 2 END as value
        from have
        group by id, event
        having value = min(value)
    ;
quit;