Hello everyone,
I was hoping someone can help me troubleshoot/finish the code below.
I have this dataset:
data have_data;
input Patid $ Visit $ Drug $ AIEXPR $ AIFCCL $ AITRLU $;
datalines;
1 0 A 0 1 1
1 12 A 1 2 3
1 24 A 1 3 2
2 0 B 0 0 0
2 12 B 1 0 0
3 0 B 3 3 2
3 12 B 2 1 1
3 24 B 1 0 0
4 0 A 0 1 0
4 24 A 1 1 2
5 0 A 0 0 1
5 12 A 0 0 0
5 24 A 3 2 3
6 0 B 2 2 .
6 12 B 1 . 1
;
run;
/* View the created dataset */
proc print data=have_data;
run;
I want to get this output in a Word document:
What I have done so far:
/* Step 1: Calculate total counts (N) for each Visit */
proc sql;
create table total_counts as
select Visit, count(*) as N
from have_data
group by Visit;
quit;
/* Step 2: Summarize data for each condition separately, including missing values */
/* Summarize AIEXPR */
proc freq data=have_data noprint;
tables Visit*Drug*AIEXPR / missing out=summary_Aiexpr(drop=percent);
run;
data summary_Aiexpr;
set summary_Aiexpr;
length Variable $6 Condition $20;
Variable = 'AIEXPR';
Condition = coalesce(AIEXPR, 'Missing'); /* Handle missing values */
drop AIEXPR;
run;
/* Summarize AIFCCL */
proc freq data=have_data noprint;
tables Visit*Drug*AIFCCL / missing out=summary_Aifccl(drop=percent);
run;
data summary_Aifccl;
set summary_Aifccl;
length Variable $6 Condition $20;
Variable = 'AIFCCL';
Condition = coalesce(AIFCCL, 'Missing');
drop AIFCCL;
run;
/* Summarize AITRLU */
proc freq data=have_data noprint;
tables Visit*Drug*AITRLU / missing out=summary_Aitrlu(drop=percent);
run;
data summary_Aitrlu;
set summary_Aitrlu;
length Variable $6 Condition $20;
Variable = 'AITRLU';
Condition = coalesce(AITRLU, 'Missing');
drop AITRLU;
run;
/* Step 3: Combine all summaries */
data summary_combined;
set summary_Aiexpr summary_Aifccl summary_Aitrlu;
run;
/* Step 4: Aggregate counts to ensure no duplicates */
proc sql;
create table summary_aggregated as
select Visit, Drug, Variable, Condition, sum(Count) as Count
from summary_combined
group by Visit, Drug, Variable, Condition;
quit;
/* Step 5: Calculate percentages within each Visit, Drug, and Variable */
proc sort data=summary_aggregated;
by Visit Drug Variable;
run;
data summary_final;
set summary_aggregated;
by Visit Drug Variable;
retain Total 0;
if first.Variable then Total = 0;
Total + Count;
/* Calculate percentages */
if Total > 0 then Percent = (Count / Total) * 100;
else Percent = 0;
/* Create formatted strings for output */
Count_Percent = catx(' ', Count, '(' || put(Percent, 5.1) || '%)');
run;
/* Step 6: Expand data to include all combinations of Visit, Drug, Variable, and Condition */
proc sql;
create table all_combinations as
select distinct a.Visit, a.Drug, b.Variable, c.Condition
from (select distinct Visit, Drug from have_data_clean) as a,
(select distinct Variable from summary_final) as b,
(select distinct Condition from summary_final) as c;
quit;
/* Merge expanded combinations with actual data */
proc sql;
create table expanded_summary as
select a.*, coalesce(b.Count_Percent, '0 (0.0%)') as Count_Percent
from all_combinations as a
left join summary_final as b
on a.Visit = b.Visit and a.Drug = b.Drug and a.Variable = b.Variable and a.Condition = b.Condition
order by Visit, Drug, Variable, Condition;
quit;
/* Step 7: Pivot data to match the desired layout */
proc transpose data=expanded_summary out=transposed_final(drop=_name_) prefix=Drug_;
by Variable Condition;
id Visit Drug;
var Count_Percent;
run;
/* Step 8: Prepare headers for the total counts (N) */
data headers;
length Header0 Header12 Header24 $30;
set total_counts end=last;
if Visit = 0 then Header0 = cats("Month 0 (N=", N, ")");
else if Visit = 12 then Header12 = cats("Month 12 (N=", N, ")");
else if Visit = 24 then Header24 = cats("Month 24 (N=", N, ")");
if last;
keep Header0 Header12 Header24;
run;
data header_trans;
set headers;
run;
I am stuck after this though. I don't know how to get it to output the table I need in Word. Any help would be appreciated.
Thanks!
... View more
