BookmarkSubscribeRSS Feed
vraj1
Quartz | Level 8

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;

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

vraj1
Quartz | Level 8

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)

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;   
vraj1
Quartz | Level 8

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;

PGStats
Opal | Level 21

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
LinusH
Tourmaline | Level 20

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
Steelers_In_DC
Barite | Level 11

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;

dcruik
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 891 views
  • 0 likes
  • 6 in conversation