topic Re: i have 2 datasets and i want to know number of missing obs from dataset a in SAS Programming

i have 2 datasets and i want to know number of missing obs from dataset a

vraj1 — Fri, 11 Dec 2015 09:31:31 GMT

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;

Re: i have 2 datasets and i want to know number of missing obs from dataset a

RW9 — Fri, 11 Dec 2015 09:43:02 GMT

Post some test data - as a datastep, and what your output should look like.

Re: i have 2 datasets and i want to know number of missing obs from dataset a

vraj1 — Fri, 11 Dec 2015 09:45:30 GMT

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)

Re: i have 2 datasets and i want to know number of missing obs from dataset a

RW9 — Fri, 11 Dec 2015 10:04:53 GMT

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;

Re: i have 2 datasets and i want to know number of missing obs from dataset a

vraj1 — Fri, 11 Dec 2015 11:15:44 GMT

I am sorry for confusing you.

I had written the following code but it is not working.-

Re: i have 2 datasets and i want to know number of missing obs from dataset a

LinusH — Fri, 11 Dec 2015 14:02:58 GMT

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.

Re: i have 2 datasets and i want to know number of missing obs from dataset a

Steelers_In_DC — Fri, 11 Dec 2015 16:38:34 GMT

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;

Re: i have 2 datasets and i want to know number of missing obs from dataset a

dcruik — Fri, 11 Dec 2015 17:30:16 GMT

Here's a way to create the output report I think you are looking for with dummy data&colon;

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;

Re: i have 2 datasets and i want to know number of missing obs from dataset a

PGStats — Sat, 12 Dec 2015 18:34:43 GMT

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)