Data Set - keeping one row from multiple instances with condition

[mesujit]

I have a small querry on below data set:
- where ever there are multiple entries of siteid, i have to keep the only one entry which has sitestatus as 'open' and siterole as 'PI', other rows of same siteid i have to delete.
Please help me to code it.
i am trying , but loosing some or the other required lines.

==== input data set====
siteid	siteStatus	siterole
544	open	PI
544	Busy	PI
565	closed or Busy	PI
567	open	PI
543	closed	PI
543	open	PI
==== output data set should be====
siteid	siteStatus	siteRrole
544	open	PI
565	closed or Busy	PI
567	open	PI
543	open	PI

[novinosrin]

Hi @mesujit  Can you please clarify why the following is in the output?

565

closed or Busy

PI

data have;
input siteid	(siteStatus	siterole) ( & $15.);
cards;
544	open	PI
544	Busy	PI
565	closed or Busy	  PI
567	open	PI
543	closed	PI
543	open	PI
;

proc sql;
 create table want(drop=_n_) as
 select *,(siteStatus='open' and siterole='PI') as _n_
 from have
 group by siteid
 having _n_ or max(_n_)=0;
quit;

[mesujit]

Hi Novinosrin,

its because the site status is not confirmed.. cant be considered as open.

[PeterClemmensen]

data have;
input siteid siteStatus $ 5-19 siterole $;
datalines;
544 open           PI
544 Busy           PI
565 closed or Busy PI
567 open           PI
543 closed         PI
543 open           PI
;

data want;
   do _N_ = 1 by 1 until (last.siteid);
      set have;
      by siteid notsorted;
   end;
   do until (last.siteid);
      set have;
      by siteid notsorted;
      if _N_ = 1 | siteStatus = "open" then output;
   end;
run;

Result:

siteid siteStatus     siterole 
544    open           PI 
565    closed or Busy PI 
567    open           PI 
543    open           PI