-1

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 ? :)

unbik
  • 178
  • 9
  • Instead of posting a duplicate question (and just changing the variable names) you should update your original question. – Negdo Sep 16 '22 at 11:20
  • You are right, but i am new here, I did not know that - I think that now this question is more clearly, do you have some idea ? :) – unbik Sep 16 '22 at 11:59
  • I would delete this duplicate if I were you, as you don't really want to irk mods. You can edit the original question with more details. But it was more or less clear. – Negdo Sep 16 '22 at 12:11

1 Answers1

0

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;
Stu Sztukowski
  • 10,597
  • 1
  • 12
  • 21