Need help generating output in Word

[Merdock]

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!

[ballardw]

You might want to loot at this page for some code and write up of making tables with N and Percent or meand and standard deviation and more into single tables.

https://communities.sas.com/t5/SAS-Communities-Library/Demographic-Table-and-Subgroup-Summary-Macro-...

Once you have something that generates the table(s) you want you use ODS to create the desired output file. While there is a direct to Word DOCX file format it is "preproduction" which is SAS-speak for "it may do what you want, it may not. "

More reliable is to create ODS RTF output which Word will open. The basic code would look like

ods rtf file="c:\path\myreportfile.rtf";

<the code that generates the report table(s) goes here>

ods rtf close;  /* this finishes the document*/

If you have not used macros the code that starts %macro and ends with %mend in the linked SAS program files has to be compilied, highlight the code and submit it. The compilation sets up allowing you to follow the instructions on using the macros to create the data that the report generator code wants.

Pay attention to any description of how the input files have to be structured and be prepared to abandon some or most of the code you show, or maybe use it. Can't tell as you didn't provide any example of your data.

[Merdock]

My dataset is this:

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;

[ChrisNZ]

See here https://documentation.sas.com/doc/en/pgmsascdc/v_054/uprint/p10mxeb6wxqfjgn1p5u0w4t8qf20.htm

ods word file="&path\test.docx";
proc print data=HAVE_DATA noobs;
run;
ods word close;

[Ksharp · Accepted solution]

data have;
    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;



proc format;
value visit
0='Month 0'
12='Month 12'
24='Month 24'
;
value fmt
.='Missing'
0='Normal'
1='Mild'
2='Moderate'
3='Severe'
;
value order_fmt
.='Missing'
0='    Normal'
1='   Mild'
2='  Moderate'
3=' Severe'
;
value $ miss(default=32)
' '='0'
;
run;


%macro var(id=,var=);
select &id. as id,
       1 as id1,"&var." as a,
       2 as id2,catx('|',put(Visit,visit.),cats('(N=',(select count(distinct Patid) from have where Visit=a.visit),')')) as b,
	   3 as id3,catx('|',catx(' ','Drug',Drug),cats('(N=',(select count(distinct Patid) from have where Visit=a.visit and Drug=a.Drug),')')) as c,
	   4 as id4,put(&var.,fmt.) as d,&var. as v,
	   5 as id5,cats(count(distinct Patid),'(',put(count(distinct Patid)/(select count(distinct Patid) from have where Visit=a.visit and Drug=a.Drug),percent7.1),')') as e
 from have as a
   group by Drug,Visit,&var. 
%mend;

proc sql;
create table want as
%var(id=1,var=AIEXPR)
union all
%var(id=2,var=AIFCCL)
union all
%var(id=3,var=AITRLU)
;
quit;
ods rtf file='c:\temp\temp.rtf' style=minimal;
proc report data=want nowd split='|' style={cellpadding=0 cellspacing=0} style(header)={fontweight=bold};
columns id id1 a v d b,c,e;
define id/group noprint;
define id1/group noprint;
define a/group  noprint ;
define v/group noprint order=formatted format=order_fmt. missing;
define d/group '' order=formatted style(column)={cellwidth=10% asis=on pretext='     '};
define b/across '';
define c/across '' nozero;
define e/group '' format=$miss. style={just=c};
compute before a/style={fontweight=bold just=l background=white};
line a $32.;
endcomp;
run;

ods rtf close;

[Merdock]

Thanks, Ksharp, that works perfectly! May I trouble you with one last question? What if I wanted to throw in a continuous variable(say lab value) along with its clinical significance (character variable) and summarize those in the same table, as well? How could I modify the code to achieve this? Updated data "have" below, along with the "want" Word document:

data have_data;
    input Patid $ Visit $ Drug $ AIEXPR $ AIFCCL $ AITRLU $ HBA1C HBA1CCS $;
    datalines;
1	0	A	0	1	1	24	0
1	12	A	1	2	3	65	0
1	24	A	1	3	2	38	0
2	0	B	0	0	0	59	1
2	12	B	1	0	0	62	0
3	0	B	3	3	2	46	0
3	12	B	2	1	1	42	1
3	24	B	1	0	0	36	1
4	0	A	0	1	0	87	0
4	24	A	1	1	2	65	0
5	0	A	0	0	1	46	1
5	12	A	0	0	0	.	
5	24	A	3	2	3	34	0
6	0	B	2	2	.	12	1
6	12	B	1	.	1	30	1
;
run;
/*View the created dataset */
proc print data=have_data;
run;

The output should contain the actual numbers, not XX.

Thanks for your help!

[Ksharp]

I think you owe me two hundred dollars.

data have;
infile cards truncover;
    input Patid $ Visit  Drug $ AIEXPR  AIFCCL  AITRLU  HBA1C HBA1CCS ;
    datalines;
1	0	A	0	1	1	24	0
1	12	A	1	2	3	65	0
1	24	A	1	3	2	38	0
2	0	B	0	0	0	59	1
2	12	B	1	0	0	62	0
3	0	B	3	3	2	46	0
3	12	B	2	1	1	42	1
3	24	B	1	0	0	36	1
4	0	A	0	1	0	87	0
4	24	A	1	1	2	65	0
5	0	A	0	0	1	46	1
5	12	A	0	0	0	.	
5	24	A	3	2	3	34	0
6	0	B	2	2	.	12	1
6	12	B	1	.	1	30	1
;
run;




proc format;
value visit
0='Month 0'
12='Month 12'
24='Month 24'
;
value fmt
.='Missing'
0='Normal'
1='Mild'
2='Moderate'
3='Severe'
;
value order_fmt
.='Missing'
0='    Normal'
1='   Mild'
2='  Moderate'
3=' Severe'
;value $ miss(default=32)
' '='0'
;
value clinic(default=32)
.='Missing'
0='Not Clinically Significant'
1='Clinically Significant'
;
value order_clinic(default=32)
.='Missing'
0=' Not Clinically Significant'
1='  Clinically Significant'
;
run;


%macro var(id=,var=);
%if %upcase(&var.)=HBA1C %then %do;

select &id. as id,
       1 as id1,"&var." as a,
       2 as id2,catx('|',put(Visit,visit.),cats('(N=',(select count(distinct Patid) from have where Visit=a.visit),')')) as b,
	   3 as id3,catx('|',catx(' ','Drug',Drug),cats('(N=',(select count(distinct Patid) from have where Visit=a.visit and Drug=a.Drug),')')) as c,
	   4 as id4,'N' as d,'  N' as v,
	   5 as id5,cats(count(&var.)) as e
 from have as a
   group by Drug,Visit
union all
select &id. as id,
       1 as id1,"&var." as a,
       2 as id2,catx('|',put(Visit,visit.),cats('(N=',(select count(distinct Patid) from have where Visit=a.visit),')')) as b,
	   3 as id3,catx('|',catx(' ','Drug',Drug),cats('(N=',(select count(distinct Patid) from have where Visit=a.visit and Drug=a.Drug),')')) as c,
	   4 as id4,'Mean (SD)' as d,' Mean (SD)' as v,
	   5 as id5,catx(' ',put(mean(&var.),8.),cats('(',put(std(&var.),8.1),')')) as e
 from have as a
   group by Drug,Visit
union all
select &id. as id,
       1 as id1,"&var." as a,
       2 as id2,catx('|',put(Visit,visit.),cats('(N=',(select count(distinct Patid) from have where Visit=a.visit),')')) as b,
	   3 as id3,catx('|',catx(' ','Drug',Drug),cats('(N=',(select count(distinct Patid) from have where Visit=a.visit and Drug=a.Drug),')')) as c,
	   4 as id4,'Median (min,max)' as d,'Median (min,max)' as v,
	   5 as id5,catx(' ',put(median(&var.),8.),cats('(',put(min(&var.),8.1),',',put(max(&var.),8.1),')')) as e
 from have as a
   group by Drug,Visit

%end;
%else %do;

select &id. as id,
       1 as id1,
%if %upcase(&var.)=HBA1CCS %then %do; "HBAIC Clinical Significance" %end; 
   %else %do; "&var." %end; as a,
       2 as id2,catx('|',put(Visit,visit.),cats('(N=',(select count(distinct Patid) from have where Visit=a.visit),')')) as b,
	   3 as id3,catx('|',catx(' ','Drug',Drug),cats('(N=',(select count(distinct Patid) from have where Visit=a.visit and Drug=a.Drug),')')) as c,
	   4 as id4,
%if %upcase(&var.)=HBA1CCS %then %do; put(&var.,clinic.) %end; 
  %else %do;put(&var.,fmt.)%end; as d,
%if %upcase(&var.)=HBA1CCS %then %do; put(&var.,order_clinic.) %end; 
  %else %do;put(&var.,order_fmt.)%end; as v,
	   5 as id5,cats(count(distinct Patid),'(',put(count(distinct Patid)/(select count(distinct Patid) from have where Visit=a.visit and Drug=a.Drug),percent7.1),')') as e
 from have as a
   group by Drug,Visit,&var. 

%end;
%mend;

proc sql;
create table want as
%var(id=1,var=AIEXPR)
union all
%var(id=2,var=AIFCCL)
union all
%var(id=3,var=AITRLU)
union all
%var(id=4,var=HBA1C)
union all
%var(id=5,var=HBA1CCS)
;
quit;
ods rtf file='c:\temp\temp.rtf' style=minimal;
proc report data=want nowd split='|' style={cellpadding=0 cellspacing=0 outputwidth=80%} style(header)={fontweight=bold};
columns id id1 a v d b,c,e;
define id/group noprint;
define id1/group noprint;
define a/group  noprint ;
define v/group noprint order=internal  missing;
define d/group ''  style(column)={asis=on pretext='     '};
define b/across '';
define c/across '' nozero;
define e/group '' format=$miss. style={just=c};
compute before a/style={fontweight=bold just=l background=white};
line a $32.;
endcomp;
run;

ods rtf close;

[Merdock]

Just two hundred dollars? I'm so grateful I might start a cult in your honor haha. Seriously though, you're a real life saver and I GREATLY appreciate your help. Thank you a million times, good sir! 🙏