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 |
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;
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;
I did a test, works perfect (as usual).
Bart
data new;
set ...;
where ID= "PG";
run;
proc print data = new;
run;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.