DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 115

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

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;

Super User
Super User
Posts: 7,413

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

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

Frequent Contributor
Posts: 115

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

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)

Super User
Super User
Posts: 7,413

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

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;   
Frequent Contributor
Posts: 115

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

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;

Respected Advisor
Posts: 4,659

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

[ Edited ]

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) 

PG
Super User
Posts: 5,260

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

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.

Data never sleeps
Valued Guide
Posts: 858

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

[ Edited ]

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;

Frequent Contributor
Posts: 130

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

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;
Ask a Question
Discussion stats
  • 8 replies
  • 269 views
  • 0 likes
  • 6 in conversation