Choose the line with proc sql and prxmatch

[SASdevAnneMarie]

Hello Experts,

I'm trying reorganise my data with proc sql : I count tne number of supports by NO_C, in case of 2 I would like to chose the Major if it's present :

input:

Want:

I'm wondering how I can use if prxmatch("m/MAJORATION/oi",lb_sup)> 0 condition in proc sql ?

Thank you !

[SASKiwi]

This is a bit ugly but here is one way based on limited data. I don't think PRXMATCH is the best option here even if it does work in SQL (I haven't tried).

data have;
  input @1 NO_C $6.
        @8 lb_sup $10.
		@19 nb_supp 2.
		@21 top_supp_major 2.
		;
datalines;
JO2886 MINIMUM    2  0
JO2886 MAJORATION 2  1
B00405 MAJORATION 2  1
B00405 ACTIV1     2  0
B00867 ACTIV1     2  0
;
run;

proc sql;
  create table want as
  select *
  from have
  where top_supp_major = 1
  or (NO_C in 
      (select NO_C
       from have
       where top_supp_major = 0
       except
       select NO_C
       from have
       where top_supp_major = 1
      )
     );
quit;