I have 2 datasets A and B.
I want to calculate number of missing observations per treatment which are present in B and not A.
I am using
proc sql;
create table stat102 as
select b.trialtrt,
"Missing" as descr2 length=40,
count(distinct a.subj_id) as cou,
"MDRD eGFR" as descr1 length=40,
10 as sort1,
2 as mdrdsort,
"Y" as calcpct
from egfr08 a full outer join egfr03 b
on a.subj_id = b.subj_id
group by b.trialtrt
;quit;
Post some test data - as a datastep, and what your output should look like.
Output should have
TRT1 TRT2
Missing N (number) N(number)
one dataset has 54 subjects and other has 55.
I want to get missing as 1 for trt1(for suppose)
Well, at a guess:
proc sql; create table WANT (COL1 char(20),TRT1 num,TRT2 num); insert into WANT set COL1="Missing" TRT1=(select count(distinct <subject_variabl>) from <your_data> where <miss_var> is null and TRT="A"), TRT2=(select count(distinct <subject_variabl>) from <your_data> where <miss_var> is null and TRT="B"); quit;
I am sorry for confusing you.
I had written the following code but it is not working.-
proc sql;
create table stat102 as
select b.trialtrt,
"Missing" as descr2 length=40,
count(distinct a.subj_id) as cou,
"MDRD eGFR" as descr1 length=40,
10 as sort1,
2 as mdrdsort,
"Y" as calcpct
from egfr08 a full outer join egfr03 b
on a.subj_id = b.subj_id
group by b.trialtrt
;quit;
The number of missing subj_id's in each dataset will be given by:
proc sql;
create table stat102 as
select
"Missing" as Condition, TRT1 label="from egfr03", TRT2 label="from egfr08"
from
(select count(distinct subj_id) as TRT1 from egfr03 where subj_id not in
(select subj_id from egfr08)) ,
(select count(distinct subj_id) as TRT2 from egfr08 where subj_id not in
(select subj_id from egfr03)) ;
quit;
(untested)
Do you mean missing observations in A?
You could do something like (untested)
sum(missing(a.subj_id) as no_of_missing_in _a
Be aware that normally any non group by column should be a result of an aggregate function.
With no example data or desired output this is just a random guess, here goes:
data a;
input id;
cards;
1
2
3
9
10
;
data b;
input id;
cards;
1
2
3
4
5
6
7
8
9
10
;
data want;
merge a(in=a)
b(in=b);
by id;
if b;
if b and not a then count+1;
run;
Here's a way to create the output report I think you are looking for with dummy data:
data A;
format Subject $10.;
do i=1 to 15;
Subject=strip(i);
output;
end;
drop i;
run;
data B;
format Subject $10.;
do i=1 to 10;
Subject=strip(i);
output;
end;
drop i;
run;
proc sql;
create table A_count as
select "Missing" as Missing,
count(Subject) as TRT1
from A
where Subject not in (select Subject from B);
create table B_count as
select "Missing" as Missing,
count(Subject) as TRT2
from B
where Subject not in (select Subject from A);
create table want as
select A.Missing,
A.TRT1,
B.TRT2
from A_count as A inner join B_Count as B
on (A.Missing=B.Missing);
quit;
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.