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
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;
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;
You're welcome
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.