The Leopard that Changed its Baseline Spots Solutions
The Leopard Changed its Baseline Spots is a quiz that separated the REPORT from the TABULATE'ers. Several possible solutions were submitted, so sincere congratulations to everyone who got the desired output.
A problem was posed as follows:
Rangers in Kruger Park were concerned to notice that some of their leopards were losing their spots. They consulted a vet who observed this was very unusual and there was no standard treatment, so they should try an experimental treatment on one group of the leopards, and a placebo treatment on the others.
The affected leopards were randomly assigned to one of the two treatments.
Over the course of a week, the park rangers rounded up the leopards, sedated them and counted their spots to establish a baseline measurement. Some leopards were counted more than once over this period. On Sunday, the park rangers collected all the leopards together and administered the treatments.
The baseline count was defined as the last non-missing count prior to treatment (days up to and including Day 0). The leopards' spots were counted again on Days 1, 2, 3, 5, 10, 20, 40 and 100 (relative to first day of dosing).
There are two downloads accompanying this quiz:
1. Program to generate the data
2. An Adobe PDF document containing the quiz and sample report.
Our original solution was created using Proc REPORT. With its flexibility and control over the rendering of output, it succeeds in matching exacting presentation requirements. Several Proc TABULATE solutions were posted often requiring less complex reporting code with the cost of subtle, but inconsequential, compromises on line drawing.
Solution 1: Proc REPORT
The first solution uses the SQL procedure to select the desired columns, perform calculations and labelling. Performing calculations for every record against a single derived value is a useful feature of the SQL language.
*Calculate the baseline for each leopard and the difference
between the day;
proc sql;
create table diff as
select leopard.subject,treatment,day,spots,base,
spots-base as diff label='Difference',
(spots-base)/base as change label='Increament change'
from work.leopard,
(select subject,spots as base 'Baseline' from work.leopard
where day<=0 and spots ne .
group by subject
having day=max(day)) as inqry
where leopard.subject=inqry.subject and day>0;
quit;
*Creates stats on diff and change;
proc means data=diff noprint nway;
class treatment day;
var diff change;
output out=summary n=dn cn
mean=dmean cmean
std=dstd cstd
min=dmin cmin
max=dmax cmax;
run;
*Transpose the data for report;
data report(keep= treatment day vstat vchange vpercent order);
set summary;
array change(5)dn dmean dstd dmin dmax;
array percent(5) cn cmean cstd cmin cmax;
array stat{5} $ ('N' 'MEAN' 'STD' 'MIN' 'MAX');
do i=1 to 5;
vchange=change{i};
vpercent=percent{i};
vstat=stat{i};
order=i;
output;
end;
run;
*Calculate how many subjects are in each treatment;
proc sql noprint;
select count(*) into:t1 from randomised where treatment=1;
select count(*) into:t2 from randomised where treatment=2;
quit;
*Creates formats for later displaying on the report;
proc format;
value treat
1="Active (N=&t1)"
2="Placebo (N=&t2)";
run;
ods listing close;
ods html style=meadow;
title1 'The Friday Quiz Project';
title2 'Change from Baseline in Leopard Spots';
footnote1 'Note: Baseline is derived as last non-missing
observation up to the end including Day 0';
footnote2
"Produced: %sysfunc(left(%qsysfunc(today(),worddate23.)))";
proc report data=report nowd missing;
column day order vstat treatment,(vchange vpercent);
define day/ group 'Day Relative to Dose';
define vstat/ group '';
define order/group order=internal noprint;
define treatment/across 'Treatment' format=treat.;
define vchange/ analysis 'Change';
define vpercent/ analysis 'Percent';
compute vchange;
if vstat ='STD' then call define(_col_,'Format','8.2');
else if vstat in ('MAX','MIN')
then call define(_col_,'Format','8.0');
else if vstat='MEAN' then call define(_col_,'Format','8.1');
endcomp;
compute vpercent;
if vstat in ('STD','MEAN','MAX','MIN')
then call define(_col_,'Format','percent8.0');
endcomp;
compute after day;
line " ";
endcomp;
run;
ods html close;
ods listing;
Solution 2: Proc TABULATE
This solution shows two options with Proc TABULATE.
A data step creates a temporary table containing results for reporting before using the randomised table as input to the FORMAT procedure which is used for generating the treatment labels which include the population size.
The first TABULATE example shows how the same layout can be achieved as with Proc REPORT, whilst the second demonstrates a second layout for this report.
data temp;
set leopard;
by subject day;
retain base_count;
if first.subject then base_count=spots;
else if day le 0 and spots ne . then base_count=spots;
if day gt 0 and spots gt 0 then do;
base_diff=sum(spots,-base_count);
base_diff_pct=base_diff/base_count;
end;
format base_diff_pct percent.;
run;
proc freq data=randomised noprint;
table treatment/out=freqout;
run;
data tmtf (keep=start label fmtname);
set freqout;
length label $20;
start=treatment;
label='Treatment ' || '(N=' || trim(left(count)) || ')';
fmtname='tmtfmt';
run;
proc format cntlin=tmtf;
run;
ods listing close;
ods html file="leopard_spots.html" style=sasweb;
proc tabulate data=temp (where=(day>0));
class day treatment;
var base_diff base_diff_pct;
table day=''*(N='n'*F=comma.
mean*F=comma.2
std='sd'*F=comma.2
min*F=comma.
max*F=comma.),
treatment='Treatment'*(base_diff='Change'
base_diff_pct='Percent'*F=percent.)
/rts=25 box='Day relative to dose';
format treatment tmtfmt.;
title1 "Change from Baseline in Leopard Spots";
run;
proc tabulate data=temp (where=(day>0));
class day treatment;
var base_diff base_diff_pct;
table day=''*
(base_diff='Change'*(N='n'*F=comma.
mean*F=comma.2
std='sd'*F=comma.2
min*F=comma.
max*F=comma.)
base_diff_pct='Percent'*(N='n'*F=comma.
mean*F=percent.
std='sd'*F=percent.
min*F=percent.
max*F=percent.)),
treatment='Treatment'
/rts=25 box='Day relative to dose'
;
format treatment tmtfmt.;
title1 "Change from Baseline in Leopard Spots";
run;
