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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.