Extract Specif observation from multiple observation

[tsureshinvites]

Hi i am very new to sas, please help out for the below scenario.

My rawdata;

data IE_Raw;
input Subject_id $ Test_Code $ Result $;
datalines;
1001 IE01 Pass
1001 IE02 Pass
1001 IE03 Pass
1001 IE04 Fail
1001 IE05 Fail
1002 IE01 Fail
1002 IE02 Pass
1002 IE03 Pass
1002 IE04 Pass
1002 IE05 Pass
1002 EX01 Pass
1002 EX02 Pass
1002 EX03 Pass
1002 EX04 Fail
1002 EX05 Fail
1003 EX01 Fail
1003 EX02 Pass
1003 EX03 Pass
1003 EX04 Pass
1003 EX05 Pass
;

Required Output:

Form the above data set need to extract record, those (subject or patient) are failed in only one test of IE tests category and the subject passed only one test in EX tests category by subject wise. like below.

Note; if a subject failed or passed more than one test, it should not be extracted.

Subject_id	Test_Code	Result
1002	IE 01	Fail
1003	EX 01	Pass

My code;

Regards,

Suresh Kumar

[ed_sas_member · Accepted solution]

Hi @tsureshinvites 

You can try the below code. You can achieve exactly the same using a proc freq and then DATA/MERGE

/* Subject are failed in only one test of IE tests category */

proc sql noprint;
	create table want1 as
	select b.subject_id, b.Test_code, b.Result
	from (select *
		  from (select subject_id, result, count(result) as count
		  		from IE_Raw
		  		where result = 'Fail' and substr(Test_code,1,2) ='IE'  
		  		group by Subject_id, result)
		  where count = 1) as a left join
		  IE_Raw as b
	on a.subject_id = b.subject_id and a.result = b.result
	where substr(Test_code,1,2) ='IE';
quit;

/* Subject_id passed / failed only one test in EX tests category */

proc sql noprint;
	create table want2 as
	select b.subject_id, b.Test_code, b.Result
	from (select *
	 	  from (select subject_id, result, count(result) as count
		  		from IE_Raw
		  		where substr(Test_code,1,2) ='EX'
		  		group by Subject_id, result)
	   	  where count = 1) as a left join
	   	  		  IE_Raw as b
	on a.subject_id = b.subject_id and a.result = b.result
	where substr(Test_code,1,2) ='EX';
quit;

/* Final merge */

data want;
	set want1 want2;
run;

[tsureshinvites]

Thanks a lot it worked fine.

[ed_sas_member]

You're welcome