topic Re: How to select observations if they ever have a specified value in different a variable? in SAS Programming

How to select observations if they ever have a specified value in different a variable?

rebelde52 — Fri, 20 Oct 2023 19:27:34 GMT

Hello ,

How could I select Observations (Rows) where if their Pending column ever says "YES" then output to a new dataset? I am thinking to use proc sql but also, I'm still learning as I go so I feel stuck. Below is my have data and want data tables.

Have

ID	Test_Date	Pending
TA	3/29/22	NO
TA	3/29/22	NO
TA	3/29/22	YES
PG	3/29/22	NO
PG	4/5/22	NO
LT	4/5/22	YES
LT	4/5/22	NO
LT	4/5/22	NO

Want

ID	Test_Date	Pending
TA	3/29/22	NO
TA	3/29/22	NO
TA	3/29/22	YES
LT	4/5/22	YES
LT	4/5/22	NO
LT	4/5/22	NO

Re: How to select observations if they ever have a specified value?

PaigeMiller — Fri, 20 Oct 2023 19:31:57 GMT

From now on, please provide data as working SAS data step code (examples and instructions) and not as Excel copy and paste. Also, you would make your own typing/coding simpler if YES/NO character strings were actually 0/1 numeric values.

proc sql;
	create table want as select *
	from have  	
	group by id
	having sum(pending='YES')>=1;
quit;

Re: How to select observations if they ever have a specified value in different a variable?

mkeintz — Mon, 23 Oct 2023 22:10:21 GMT

This is a data step solution, using a hash object containing the only id's that have any instance of 'YES':

Untested, in the absence of sample data in the form of a working data step.

data want ;
  set have;
  if _n_=1 then do;
    declare hash ids_with_yes (dataset:'have (keep=id pending where=(pending="YES"))');
      ids_with_yes.definekey('id');
      ids_with_yes.definedata('id');
      ids_with_yes.definedone();
  end;
  if ids_with_yes.check()=0;
run;

Re: How to select observations if they ever have a specified value in different a variable?

Kondala — Fri, 27 Oct 2023 09:26:50 GMT

data new;

set ...;

where ID= "PG";

run;

proc print data = new;

run;

Re: How to select observations if they ever have a specified value in different a variable?

yabwon — Fri, 27 Oct 2023 09:46:18 GMT

Hi @rebelde52 ,

@PaigeMiller and @mkeintz already gave you great solutions, so I'm adding this on just for fun.

data have;
input ID $	Test_Date :mmddyy8.	Pending $;
format Test_Date mmddyy10.;
cards;
TA 3/29/22 NO
TA 3/29/22 NO
TA 3/29/22 YES
PG 3/29/22 NO
PG 4/5/22 NO
LT 4/5/22 YES
LT 4/5/22 NO
LT 4/5/22 NO
;
run;
proc print;
run;

data want;
  _N_=0;
  do until(last.ID);
    set have;
    by ID notsorted;
    _N_ + Pending="YES";  
  end;

  do until(last.ID);
    set have;
    by ID notsorted;
    if _N_ then output; 
  end;
run;
proc print;
run;

Bart

{EDIT:}

One more for "lazy typers":

%macro DoW(code);
  do until(last.ID);
    set have;
    by ID notsorted;
    &code.;  
  end;
%mend;

data want;
  _N_=0;
  %DoW(_N_ + Pending="YES")  
  %DoW(if _N_ then output) 
run;

Re: How to select observations if they ever have a specified value in different a variable?

yabwon — Fri, 27 Oct 2023 09:41:14 GMT

I did a test, works perfect (as usual).

Bart