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..
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:
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:
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;
Hi @Elma
Awesome
Normally the %missing (non cumulative) is in the table 'want'.
The cumulative percentages are in the table 'want_cum'
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
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,
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.
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,
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.