Table design troubles with headers and sub-headers (using macros and proc report)

[Iamhumerus77]

Hello everyone, 

I am working with existing code to create tables that will export as RTF files.  The following is the SAS code I used to create this table:

******************************************************** Macro #1: Table numbers, titles and footnotes ******************************************************************;

%* Import spreadsheet data with table numbers and titles. Tables that display only 1 variable are identified by their variable name and age group (_1 suffix for age 
  group 13-17 and _2 for ages 18-24). Stacked tables showing >1 variable use a descriptive name along with the age group suffix. Tables that include both age groups
  or that are not age-specific (e.g., sample demographics) do not include a suffix.;

*edit to datafile path below, refer to original;
proc import datafile=" " 
out=tableinfo
dbms=xlsx replace;
run;
%* put table info into SAS datasets to use as input for proc format;

proc sql;
  create table tnum as
  select TableID as start,TableNum as label,'c' as type,"tabnfmt" as fmtname
  from tableinfo;
  create table title as
  select TableID as start,TableTitle as label,'c' as type,"titlefmt" as fmtname
  from tableinfo;
  create table footn1 as
  select TableID as start,Footnote1 as label,'c' as type,"foot1fmt" as fmtname
  from tableinfo;
  create table footn2 as
  select TableID as start,Footnote2 as label,'c' as type,"foot2fmt" as fmtname
  from tableinfo;
  create table footn3 as
  select TableID as start,Footnote3 as label,'c' as type,"foot3fmt" as fmtname
  from tableinfo;
  create table footn4 as
  select TableID as start,Footnote4 as label,'c' as type,"foot4fmt" as fmtname
  from tableinfo;
  create table footn5 as
  select TableID as start,Footnote5 as label,'c' as type,"foot5fmt" as fmtname
  from tableinfo;
  create table footn6 as
  select TableID as start,Footnote6 as label,'c' as type,"foot6fmt" as fmtname
  from tableinfo;
create table footn7 as
  select TableID as start,Footnote7 as label,'c' as type,"foot7fmt" as fmtname
  from tableinfo;
  create table footn8 as
  select TableID as start,Footnote8 as label,'c' as type,"foot8fmt" as fmtname
  from tableinfo;
  create table footn9 as
  select TableID as start,Footnote9 as label,'c' as type,"foot9fmt" as fmtname
  from tableinfo;
  create table footn10 as
  select TableID as start,Footnote10 as label,'c' as type,"foot10fmt" as fmtname
  from tableinfo;
quit;

%* use cntlin option to read datasets into proc format;

proc format library=work cntlin=tnum; run;
proc format library=work cntlin=title; run;
proc format library=work cntlin=footn1; run;
proc format library=work cntlin=footn2; run;
proc format library=work cntlin=footn3; run;
proc format library=work cntlin=footn4; run;
proc format library=work cntlin=footn5; run;
proc format library=work cntlin=footn6; run;
proc format library=work cntlin=footn7; run;
proc format library=work cntlin=footn8; run;
proc format library=work cntlin=footn9; run;
proc format library=work cntlin=footn10; run;

%* sysfunc uses the putc function to get the formatted values for the Table number and title -- the %global statement is necessary so the macro variables which are 
  created inside the macro TableInfo can be called in another macro in the Final Report Tables program. (Without the %global statement they would be created as local
  macro variables and thus only capable of being called within the macro in which they were created.);

%global TableNumber TableTitle Footnote1 Footnote2 Footnote3 Footnote4 Footnote5 Footnote6 Footnote7 Footnote8 Footnote9 Footnote10;

%MACRO TableInfo(table=);
  %let TableNumber = %sysfunc(putc(&table,tabnfmt));
  %let TableTitle = %sysfunc(putc(&table,titlefmt));
  %let Footnote1 = %sysfunc(putc(&table,foot1fmt));
  %let Footnote2 = %sysfunc(putc(&table,foot2fmt));
  %let Footnote3 = %sysfunc(putc(&table,foot3fmt));
  %let Footnote4 = %sysfunc(putc(&table,foot4fmt));
  %let Footnote5 = %sysfunc(putc(&table,foot5fmt));
  %let Footnote6 = %sysfunc(putc(&table,foot6fmt));
  %let Footnote7 = %sysfunc(putc(&table,foot7fmt));
  %let Footnote8 = %sysfunc(putc(&table,foot8fmt));
  %let Footnote9 = %sysfunc(putc(&table,foot9fmt));
  %let Footnote10 = %sysfunc(putc(&table,foot10fmt));
%MEND TableInfo;

%* creates a 0/1 value where 1 indicates the macro variable, i.e., footnote is blank;

%macro isBlank1;
  %sysevalf(%superq(Footnote1)=,boolean)
%mend isBlank1;
%macro isBlank2;
  %sysevalf(%superq(Footnote2)=,boolean)
%mend isBlank2;
%macro isBlank3;
  %sysevalf(%superq(Footnote3)=,boolean)
%mend isBlank3;
%macro isBlank4;
  %sysevalf(%superq(Footnote4)=,boolean)
%mend isBlank4;
%macro isBlank5;
  %sysevalf(%superq(Footnote5)=,boolean)
%mend isBlank5;
%macro isBlank6;
  %sysevalf(%superq(Footnote6)=,boolean)
%mend isBlank6;
%macro isBlank7;
  %sysevalf(%superq(Footnote7)=,boolean)
%mend isBlank7;
%macro isBlank8;
  %sysevalf(%superq(Footnote8)=,boolean)
%mend isBlank8;
%macro isBlank9;
  %sysevalf(%superq(Footnote9)=,boolean)
%mend isBlank9;
%macro isBlank10;
  %sysevalf(%superq(Footnote10)=,boolean)
%mend isBlank10;
/************************************************************ Macro #2 Demographics ***********************************************************************************;

Purpose:  Runs proc surveyfreq to produce the percent distribution of all responses for 5 separate demographic variables that are stacked and printed in 1 table 
  separated by subheadings. Each variable or group sums to 100 percent (may not sum to 100.0 exactly due to rounding). The Proc Report code is in the Final Reports
  program.

Macro Demographics consists of 3 macro variables:

	&var1 			Analysis variable

	&demgrp 		Subheading for the demographic group that appears in the table, e.g., Education Status

	&abbr 			An abbreviation or shortened version of the subheading name that appears in the sub-total line for each demographic group, e.g., Education *****/

* change made below so i can specify by age %Macro Demographics(var1=,demgrp=,abbr=);

%Macro Demographics(var1=,age=,demgrp=,abbr= );
ods show;
ods output crosstabs = freq1;
ods select report;

proc sort data=DEMOGF; by gender; run;

proc surveyfreq data=DEMOGF nomcar; 
	table gender * agegrp*&var1./ row col cl deff; 
	strata &stratumvar.; 
	cluster &clustervar.; 
	weight &weightvar.; 
run;

data freq2; set freq1; 
		where agegrp = &age.;
		keep gender rowpercent rowlowercl rowuppercl f_&var1. frequency wgtfreq table;
		run;
	%*create column header variable and Percent + confidence limits variable; 
	data &var1; length subhead $ 100;
    set freq2(keep=gender rowpercent rowlowercl rowuppercl f_&var1. frequency wgtfreq table); *original:set freq1(keep=gender rowpercent rowlowercl rowuppercl f_&var1. frequency wgtfreq table);
		format Header pct $100.; 
		Header=strip(f_&var1.);
	    if header="Total" then pct= catx("", gender, "(n=",strip(put(Frequency,comma6.)),")"); *old code:("100.0 ","(",strip(put(Frequency,comma6.)),")");
	    else pct= cat(strip(put(rowpercent,8.1)),"         (",strip(put(rowlowercl,8.1))," - ",strip(put(rowuppercl,8.1)),")");
	    if header="Total" then header="&demgrp";
		subhead="&demgrp";
		keep gender subhead header frequency pct; 
		run;

%Mend Demographics;

%let year=2014;
%let country=Zambia;
%let ctry_adj=Zambian;			
%let citizens=Zambians;		

** change the path to where the rtf output should be sent; 

%let outfile= ;

** change these values if the variables used in PROC SURVEYFREQ and the age in years variable changes;

%let stratumvar= PROV_CODE; 		
%let clustervar= PSU; 		
%let weightvar= FINALWGT; 	

libname combined " "; 

** minoperator option turns on the IN operator in macro language;

OPTIONS spool mlogic mprint symbolgen nofmterr missing=" " nonumber nodate ls=150 ps=66 minoperator noquotelenmax orientation=portrait;


** read in macros;

*%Include "  "; 

%Include " "; 

** turn on ODS trace and define escape character symbol;

ods trace on;
ods escapechar="^";
* KEEPN option does not allow a table to split at a page break unless the entire table cannot fit on one page;

*-----------------------------------*
* Chapter 2: Demographics Tables	*
*-----------------------------------;
ods rtf keepn startpage=no file="&OUTFILE\&country.&year._table_chapter2_demographic_changes_%sysfunc(today(), mmddyy6.).rtf";

*************** MULTIPLE VARS. (% distribution of males/females for select background characteristics): demog1_1 in Tables Information xlsx ***************;

%Demographics(var1=school, age=1, demgrp=Education Status, abbr=);*deleted:"education from abbr= to see if it changes the label in the total row,;
%Demographics(var1=HOHCAT, age=1, demgrp=Reported Head of Household Age, abbr=Head of Household Age);
%Demographics(var1=orphan1317, age=1, demgrp=%str(Orphanhood^{super 1} ^S={fontweight=medium}(13-17 year olds)), abbr=%str(Orphanhood 13-17));
%Demographics(var1=ework1317,age=1, demgrp=Ever worked for money or any other payment in the past year 13-17,abbr=Ever worked 13-17);



data demog1_1; set school hohcat orphan1317 ework1317; run;

%TableInfo(table=demog1_1)

%* suppress SAS System title;
 title;

proc report data=demog1_1 spanrows nofs nowd split='|'
   style(report)=[font_face=Calibri] 
   style(column)=[font_face=Calibri font_size=10pt just=c font_weight=medium] 
   style(header)=[font_face=Calibri background=white font_size=11pt font_weight=bold just=c] 
   style(lines)={just=l font=("Calibri",11pt,bold) bordertopcolor=white borderleftcolor=white borderrightcolor=white}; 
columns subhead ("^S={bordertopcolor=black}" header) (gender,(pct)); 
define subhead / order order=data group noprint;
define header / order order=data "" group style(header)=[bordertopcolor=white] style(column)=[cellwidth=4.5in];
define gender / across "" descending format=genderfmt. order=data; 
define pct /"Percent|^S={fontweight=medium font_size=10pt}(95% CI^{super ^{unicode 00A7}})" group style=[cellwidth=.9in]; 
compute before subhead / style=[font_weight=bold just=L font_size=10pt borderleftcolor=black borderrightcolor=black background=#BFBFBF];
line @1 "^S={bordertopcolor=black}" subhead $100.;
endcomp;
compute header;
  if index(header,"(n)")>0 then call define(_row_,"STYLE","STYLE=[font_weight=bold]");
endcomp;
compute before _page_ /style=[borderbottomcolor=black bordertopcolor=black borderleftcolor=black borderrightcolor=black background=#C5D9F1]; *added:/style=[borderbottomcolor=black bordertopcolor=black borderleftcolor=black borderrightcolor=black background=#C5D9F1];
     line "^{style [color=#963634] Table} &TableNumber.. &TableTitle.";
endcomp;
compute after _page_ / style=[font=("Calibri",9pt,medium) borderbottomcolor=white bordertopcolor=black];
 line "&Footnote1"; 
 line "&Footnote2"; 
 line "&Footnote3";
 line "&Footnote4";
 line "&Footnote5";
 line "&Footnote6";
endcomp;
run;
******************try to make the new table 3.1.2 Location of work among 13-17-year-olds who have worked in the past year. Tale work_1 – ***********************************************************************;


%Demographics(var1=work1, age=1,demgrp=worked);
data work_1; set work1; run;

%TableInfo(table=work_1)

%* suppress SAS System title;
 title;
proc report data=work_1 spanrows nofs nowd split='|'
   style(report)=[font_face=Calibri] 
   style(column)=[font_face=Calibri font_size=10pt just=c font_weight=medium] 
   style(header)=[font_face=Calibri background=white font_size=11pt font_weight=bold just=c] 
   style(lines)={just=l font=("Calibri",11pt,bold) bordertopcolor=white borderleftcolor=white borderrightcolor=white}; 
columns subhead ("^S={bordertopcolor=black}" header) (gender,(pct)); 
define subhead / order order=data group noprint;
define gender / across "" descending format=genderfmt. order=data; 
define header / order order=data "" group style(header)=[bordertopcolor=white] style(column)=[cellwidth=4.5in];
define pct /"Percent|^S={fontweight=medium font_size=10pt}(95% CI^{super ^{unicode 00A7}})" group style=[cellwidth=.9in]; 
compute header;
  if index(header,"(n)")>0 then call define(_row_,"STYLE","STYLE=[font_weight=bold]");
endcomp;
compute before _page_ /style=[borderbottomcolor=black bordertopcolor=black borderleftcolor=black borderrightcolor=black background=#C5D9F1];
   line "^{style [color=#963634] Table} &TableNumber.. &TableTitle.";
endcomp;
compute after _page_ / style=[font=("Calibri",9pt,medium) borderbottomcolor=white bordertopcolor=black];
 line "&Footnote1"; 
 line "&Footnote2"; 
 line "&Footnote3";
 line "&Footnote4";
 line "&Footnote5";
 line "&Footnote6";
 line "&Footnote7";
endcomp;
run;
ods rtf close;
proc printto; run;

and this is what I want my table to look like: 

To me it appears that my biggest issue is that the header for the total (n) frequency for each category is on the bottom of each category instead of the top (where the subhead is). I've tried moving things around, but I haven't been able to fix it. Any advice is appreciated. Thank you!

[PaigeMiller]

In my experience and in my opinion, the best way to figure out how to fix this code is to not use macros at the start. Just modify the PROC REPORT code so that it runs without any macros and without any macro variables, as if you had typed it in yourself from scratch; and get it to do exactly what you want. Then add the macro variables and macros back in, this will be much easier than if you try to fix the macro in its entirety.

Regarding getting the HEADER column to appear in the order you want, there are a number of tricks. When I absolutely must have a specific order, I will create a new variable, which has the desired order, the first one gets number 1, the next in order gets number 2 and so on. So, a trivial example, I want to see the data in SASHELP.CLASS, but I want data for Henry to be the last row. Note that I create a new variable named N that has the desired order, and in PROC REPORT I use order=internal to make sure that the values of N are used in determining the order.

data class;
    set sashelp.class;
    n=_n_;
    if name='Henry' then n=9999;
run;

proc report data=class;
    columns n name age sex height weight;
    define n/order order=internal noprint;
    define name/display;
run;

[Iamhumerus77]

Thank you for the advice, I tried that with my dataset. However, it seems like it is treating each cell as it's own row. For example if I set n=0 for "worked" it will split the value for males and females so they aren't on the same row and then none of the values for the other categories will show or it will separate and there will be multiple rows of the same category that has either the value for male or female shown. I'm thinking this might be because it treats the categories and their values as separate variables (see below)

Work_1

Obs subhead gender Frequency Header pct

1 worked 1 21 Family Dwelling 54.6 (36.2 - 72.9)

2 worked 1 1 FormalOffice 1.2 (0.0 - 3.6)

3 worked 1 5 Food/Retail/Hospitality 9.8 (0.5 - 19.1)

4 worked 1 4 Factory/Construction 6.5 (0.1 - 12.8)

5 worked 1 13 Agriculture and Fisheries 28.0 (11.3 - 44.7)

6 worked 1 0 Different Places ( - 7;

7 worked 1 44 worked 1 (n= 44 )

8 worked 2 4 Family Dwelling 25.9 (0.0 - 52.0)

9 worked 2 0 FormalOffice ( - )

10 worked 2 5 Food/Retail/Hospitality 16.1 (0.5 - 31.7)

11 worked 2 0 Factory/Construction ( - )

12 worked 2 15 Agriculture and Fisheries 53.2 (27.2 - 79.2)

13 worked 2 2 Different Places 4.8 (0.0 - 11.5)

14 worked 2 26 worked 2 (n= 26 )

I am not sure how to go about remedying that though. 

[PaigeMiller]

---

@Iamhumerus77 wrote:

Thank you for the advice, I tried that with my dataset. However, it seems like it is treating each cell as it's own row. For example if I set n=0 for "worked" it will split the value for males and females so they aren't on the same row and then none of the values for the other categories will show or it will separate and there will be multiple rows of the same category that has either the value for male or female shown. I'm thinking this might be because it treats the categories and their values as separate variables (see below)

---

The specific PROC REPORT code I provided does indeed treat each row in the data set as one row in the PROC REPORT output. That's because I picked a simple and trivial example to illustrate the "trick", not to illustrate a working PROC REPORT for your data. You need to write PROC REPORT code for your data that produces the output you want, except for the ordering, and get that to work first. Then add in this trick into your data set and into PROC REPORT to produce the desired specific ordering of the rows.