topic Re: Data Set - keeping one row from multiple instances with condition in SAS Programming

Data Set - keeping one row from multiple instances with condition

mesujit — Fri, 21 Aug 2020 13:19:22 GMT

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

Re: Data Set - keeping one row from multiple instances with condition

novinosrin — Fri, 21 Aug 2020 16:58:10 GMT

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

565

closed or Busy

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;

Re: Data Set - keeping one row from multiple instances with condition

PeterClemmensen — Fri, 21 Aug 2020 13:30:50 GMT

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

Re: Data Set - keeping one row from multiple instances with condition

mesujit — Fri, 21 Aug 2020 17:17:47 GMT

Hi Novinosrin,

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