topic Re: Report table with missing percentage visit*treatment in SAS Programming

Report table with missing percentage visit*treatment

Elma — Thu, 27 Feb 2020 10:21:35 GMT

Hi all,

I have a horizontal repeated measurements data set that looks like this:

Id trt visit1 visit2 visit3

1 1 2 3 .

2 1 3 4 .

3 2 . . .

4 2 4 . .

where trt=treatment.

1) I would like to create a table with % missing percentage for each visit variable by treatment (like the below table):

	treatment
	1	2
visit1	%missing	%missing
visit2	%missing	%missing
visit3	%missing	%missing

I've used

proc means data=want nmiss N;

by trt;
var visit1 visit2 visit3;

run;

but the result is in different treatment tables and there is no missing percentage..

2) I would like to create the same table with accumulate % missing percentages towards the last visit by treatment.

I've also read the attached but with no outcome..

Any help would be appreciated..

Re: Report table with missing percentage visit*treatment

ed_sas_member — Thu, 27 Feb 2020 10:56:21 GMT

Hi @Elma

Here is an approach to do this:

data have;
	input Id   trt   visit1  visit2  visit3;
	datalines;
1     1      2       3      .
2     1      3       4      .
3     2      .        .       .
4     2      4       .       .
;
run;

proc sql;
	create table have_stat as
	select trt,
		   nmiss(visit1) / count(trt) as visit1 format=percent8.2,
		   nmiss(visit2) / count(trt) as visit2 format=percent8.2,
		   nmiss(visit3) / count(trt) as visit3 format=percent8.2
	from have
	group by trt;
quit;

proc transpose data=have_stat out=have_tr name=visit;
	var visit:;
	by trt;
run;

proc sort data=have_tr;
	by visit trt;
run;

proc transpose data=have_tr out=want (drop=_:);
	var col1;
	id trt;
	by visit;
run;

Output:

Re: Report table with missing percentage visit*treatment

ed_sas_member — Thu, 27 Feb 2020 11:27:32 GMT

Another approach:

data have;
	input Id   trt   visit1  visit2  visit3;
	datalines;
1     1      2       3      .
2     1      3       4      .
3     2      .        .       .
4     2      4       .       .
;
run;

/* Missing visits */
proc means data=have noprint;
	var visit1  visit2  visit3;
	class trt;
	ways 1;
	output out=have_miss (drop=_:) nmiss=;
run;

proc transpose data=have_miss out=have_nmiss_tr (rename=(col1=nmiss)) name=visit;
	var visit:;
	by trt;
run;

/* Total visits */
proc means data=have noprint;
	var trt;
	class trt;
	ways 1;
	output out=have_n (drop=_:) n=n /autoname;
run;

/* Merge */
data have_tr;
	merge have_n have_nmiss_tr;
	by trt;
	
	if first.trt then call missing (nmiss_cum, n_cum);
	nmiss_cum + nmiss;
	n_cum + n;
	
	format pct_missing pct_cum_missing percent8.2;
	pct_missing = nmiss/n;
	pct_cum_missing = nmiss_cum / n_cum;
	
	drop n:;
run;

proc sort data=have_tr;
	by visit trt;
run;

proc transpose data=have_tr out=want (drop=_:); /* dataset want: % missing */
	var pct_missing;
	id trt;
	by visit;
run;

proc transpose data=have_tr out=want_cum (drop=_:); /* dataset want_cum: % missing cum */
	var pct_cum_missing;
	id trt;
	by visit;
run;

Re: Report table with missing percentage visit*treatment

Elma — Thu, 27 Feb 2020 11:35:00 GMT

Thanks a lot!Works great and gives the cumulative %missing.

Is it a way to adjust the code to take the simple (not accumulated)
%missing?

Re: Report table with missing percentage visit*treatment

ed_sas_member — Thu, 27 Feb 2020 11:39:28 GMT

Hi @Elma

Awesome

Normally the %missing (non cumulative) is in the table 'want'.

The cumulative percentages are in the table 'want_cum'

Re: Report table with missing percentage visit*treatment

Elma — Thu, 27 Feb 2020 13:32:17 GMT

Well, as I have a monotone dataset (once dropout, forever dropout), what I want is the 'Want ' dataset .

For example if 5% is missing at visit1 and then another 6% at visit2, then in total 11% are missing at visit2, and so on (by treatment).

So everything great until here.

I'm trying to understand what the 'want_cum' includes as percentages

Re: Report table with missing percentage visit*treatment

ed_sas_member — Thu, 27 Feb 2020 13:39:38 GMT

Hi @Elma

If we take the following example:

data have;
	input Id   trt   visit1  visit2  visit3;
	datalines;
1     1      2       3      .
2     1      3       4      .
3     2      .        .       .
4     2      4       .       .

-> for trt=2, at visit 1: we have 1 missing visit out of 2 visits (50%). At visit 2, we have 2 missing visits out of 2 visits (100%).

In a cumulative approach, at visit2, we have 4 visits (2 at visit 1 + 2 at visit 2) and 3 missing values -> 75%. This is what the code computes. Is this kind of approach relevant in your case? Otherwise, which cumulative percentage would be the right value in this case?

Best,

Re: Report table with missing percentage visit*treatment

Elma — Fri, 28 Feb 2020 08:11:09 GMT

Yep, my mistake. As "cummulative" I meant the %missing from the derived 'want' table.

So no need for the 'want_cum'.

Many thanks for solving.

Re: Report table with missing percentage visit*treatment

ed_sas_member — Fri, 28 Feb 2020 08:39:52 GMT

Hi @Elma

No worries.

I am a bit confused about the calculation for cumulative %missing.

Could you please explain how you want to calculate it from an example?

Best,

Re: Report table with missing percentage visit*treatment

Elma — Tue, 03 Mar 2020 06:25:58 GMT

Well, that was my point in the previous post.
From the beginning I wanted only one % percent missing: the %missing (from the "want" table): due to the fact that the dataset is monotone, if at visit1 the dropout rate is for example 5%, an added dropout rate of for example 4% at visit2 would yield a total dropout rate of 9% and so on. That was what I meant as "cummulative".
The second % percent: "want_cum" deriving from the code has no practice.

Many thanks