Hi @tsureshinvites
Here is a way to do that using data / proc step
But in my opinion, proc sql remains the simplest way to do that.
proc freq data=raw_data noprint;
table Subject_id * Result / out=stat_IE (where=(count=1) drop=percent);
where substr(Test_code,1,2) = "IE" and Result = "Fail";
run;
/* IE */
data stat_IE2;
if _n_ = 1 then do;
declare hash h (dataset:'stat_IE');
h.definekey('Subject_id','Result');
h.definedone();
end;
set raw_data;
where substr(Test_code,1,2) = "IE";
if h.find() = 0 then output;
run;
/* EX */
proc freq data=raw_data noprint;
table Subject_id * Result / out=stat_EX (where=(count=1) drop=percent);
where substr(Test_code,1,2) = "EX" and Result = "Pass";
run;
data stat_EX2;
if _n_ = 1 then do;
declare hash h (dataset:'stat_EX');
h.definekey('Subject_id','Result');
h.definedone();
end;
set raw_data;
where substr(Test_code,1,2) = "EX";
if h.find() = 0 then output;
run;
/* Final dataset : dataset WANT*/
data want;
if _n_ = 1 then do;
declare hash h1 (dataset:'stat_EX2');
h1.definekey('Subject_id');
h1.definedone();
declare hash h2 (dataset:'stat_IE2');
h2.definekey('Subject_id');
h2.definedone();
end;
set stat_EX2 stat_IE2;
if h1.find() = 0 and h2.find() = 0 then output;
run;
... View more
