topic Re: Proc tabulate getting n's and col percents on the same line in SAS Programming

Proc tabulate getting n's and col percents on the same line

claremc — Wed, 13 Jan 2021 14:49:19 GMT

Hello,

I am currently trying to use proc tabulate to create a demographics table. I want the frequency of each variable (the n) to appear in the same box as the column percent in parentheses. I ran this code after reading https://support.sas.com/resources/papers/proceedings13/289-2013.pdf :

proc format;
 picture paren (round)
 low-high = '(009.9)'
 ( prefix = '(' );
run; 

proc tabulate data=mtch_abcovng5;
 class age_4cat;
 table age_4cat=''*(n = '' * [style=[just=R cellwidth=90 borderrightstyle = hidden] ]
 pctn = '' * [style=[just=L cellwidth=110 borderleftstyle = hidden] f=paren.]);
/*  format age_4cat age_4catl.; */
run;

My table came out how I want it to -

But when I run my actual demographics table employing the exact same format as above, the column percents appear below the number in a separate row (code and screenshot below).

proc tabulate data = mtch_abcovng5 missing s=[foreground=black just=c cellwidth=700 ]; 
class fresult  age_4cat race_f faccode cdms_lab; 
var age1_new; 
tables  age1_new="Age at collection date (Mean)"*(mean=' ')
		age_4cat="Age categorized"*(n = '' * [style=[just=R cellwidth=90 borderrightstyle = hidden] ]
		 pctn = '' * [style=[just=L cellwidth=110 borderleftstyle = hidden] f=paren.])
	    race_f = "Race/ethnicity"*(n = '' * [style=[just=R cellwidth=90 borderrightstyle = hidden] ]
	    	 pctn = '' * [style=[just=L cellwidth=110 borderleftstyle = hidden] f=paren.])
	    faccode = "Ordering facility for negatives"*(n = '' * [style=[just=R cellwidth=90 borderrightstyle = hidden] ]
	     pctn = '' * [style=[just=L cellwidth=110 borderleftstyle = hidden] f=paren.])
	    cdms_lab = "Ordering facility for positives"*(n = '' * [style=[just=R cellwidth=90 borderrightstyle = hidden] ]
	    pctn = '' * [style=[just=L cellwidth=110 borderleftstyle = hidden] f=paren.]),
	    fresult*([style=[cellwidth=110]]);
title "Demographics of AB from April 20th to September 1st"; 
title2 "Excluding people with both AB and PCR tests"; 	
where ((ab_colldate_new lt '01SEP2020'd and ab_colldate_new ne .) or (neg_colldate lt "01SEP2020"d and neg_colldate ne . and neg_test = 'AB')) ;
run;

Any ideas of how to get the column percents on the same line here?

Thanks,

Clare

Re: Proc tabulate getting n's and col percents on the same line

ballardw — Wed, 13 Jan 2021 15:28:57 GMT

Proc Tabulate will not place two statistics "in the same box".

You can use a style or override style settings for cell boundaries so the "box" does not appear but the values will be in different cells.

So, do you want the appearance of being in the same "cell", or do you actually want the values to be in a single "cell".

Note: if you want things to appear side by side then the statistic has to be in the COLUMN dimension. The construct you are using has the statistics in the ROW dimension.

The Table statement in Proc tabulate when you have two dimensions have everything before the first comma as the row dimension so:

Table classvariable *(statistic otherstatistic) 
         ,
         otherclassvar anotherclassvar
       / <table options>
;

Places the statistics on two rows for each level of Classvariable. Note that I place the comma separating the dimensions where it is easy to see.

If you want the statistics side by side then the structure likely would be:

Table classvariable
         ,
        ( otherclassvar anotherclassvar)  *(statistic otherstatistic)  
       / <table options>
;

Re: Proc tabulate getting n's and col percents on the same line

claremc — Wed, 13 Jan 2021 16:08:35 GMT

Yay that worked thanks so much!

Re: Proc tabulate getting n's and col percents on the same line

claremc — Wed, 13 Jan 2021 16:11:55 GMT

Thanks so much -- that worked! One follow-up question -- do you know how to format the cell for ONLY continuous variables? So putting the mean (SD) for a continuous variable but keeping the formatting for class variables? I have one continuous variable that needs to be in mean (SD) format. See screenshot below.

I have tried adding this formatting statement below which I think I would then apply to the continuous variable, but I'm not sure where to add it.

proc format; 
 picture lcl (round) /* Lower limit requires a left parenthesis and a comma on the right */
 low-high = '009.99,'
 ( prefix = '(' );
 picture ucl (round)
 low-high = '009.99)'; /* Upper limit requires only a right parenthesis */

run;

Thanks,

Clare

Re: Proc tabulate getting n's and col percents on the same line

ballardw — Wed, 13 Jan 2021 16:39:48 GMT

@claremc wrote:

Thanks so much -- that worked! One follow-up question -- do you know how to format the cell for ONLY continuous variables? So putting the mean (SD) for a continuous variable but keeping the formatting for class variables? I have one continuous variable that needs to be in mean (SD) format. See screenshot below.

I have tried adding this formatting statement below which I think I would then apply to the continuous variable, but I'm not sure where to add it.
proc format; 
 picture lcl (round) /* Lower limit requires a left parenthesis and a comma on the right */
 low-high = '009.99,'
 ( prefix = '(' );
 picture ucl (round)
 low-high = '009.99)'; /* Upper limit requires only a right parenthesis */

run; 
Thanks,

Clare

I'm not quite sure I understand the question.

It sounds like you want an N and Pctn of the Mean??? Tabulate won't cross statistics, you can't get an "n" of the "mean" if you want Tabulate to calculate the mean.

If I was doing something similar to this in Proc Tabulate I would be tempted to have another table statement (you can have LOTS of tables in one call to proc tabulate) that just did the single variable

tables age ,

otherclassvar *(mean sd)

;

So the layout was similar to the other. There would be a space between the tables though. To make things look nice you may get into controlling the cell sizes with cellwidth style overrides so they align from table bit to table.

Re: Proc tabulate getting n's and col percents on the same line

claremc — Thu, 14 Jan 2021 20:02:46 GMT

Thank you!!! I am going to separate out the continuous variables in a different table statement.

One more question..... I noticed my percents being output are not column percents, rather they are total percents... Here is my final code.

proc format;
 picture paren (round)
 low-high = '(009.9)'
 ( prefix = '(' );
 run; 

proc tabulate data = mtch_abcovng5 missing s=[foreground=black just=c cellwidth=700 ]; 
class fresult  age_4cat race_f faccode cdms_lab; 
var age1_new; 
tables  age1_new="Age at collection date (Mean)"
		age_4cat="Age categorized"
	    race_f = "Race/ethnicity"
	    faccode = "Ordering facility for negatives"
	    cdms_lab = "Ordering facility for positives",
	    fresult = "AB result"*(n='' * [style=[just=R cellwidth=90 borderrightstyle = hidden] ]
	    		 pctn = '' * [style=[just=L cellwidth=110 borderleftstyle = hidden] f=paren.]);
title "Demographics of AB from April 20th to September 1st"; 
title2 "Among people with only AB tests (no PCR or both)"; 	
where ((ab_colldate_new lt '01SEP2020'd and ab_colldate_new ne .) or (neg_colldate lt "01SEP2020"d and neg_colldate ne . and neg_test = 'AB')) ;
run;

Any idea how to make it column?

Thanks!

Clare

Re: Proc tabulate getting n's and col percents on the same line

claremc — Thu, 14 Jan 2021 20:16:09 GMT

Nevermind - I figured it out! I added colpctn instead of just pctn.

proc tabulate data = mtch_abcovng5 missing s=[foreground=black just=c cellwidth=700 ]; 
class fresult  age_4cat race_f faccode cdms_lab; 
var age1_new; 
tables  age1_new="Age at collection date (Mean)"
		age_4cat="Age categorized"
	    race_f = "Race/ethnicity"
	    faccode = "Ordering facility for negatives"
	    cdms_lab = "Ordering facility for positives",
	    fresult = "AB result"*(n='' * [style=[just=R cellwidth=90 borderrightstyle = hidden] ]
	    		 colpctn = '' * [style=[just=L cellwidth=110 borderleftstyle = hidden] f=paren.]);
title "Demographics of AB from April 20th to September 1st"; 
title2 "Among people with only AB tests (no PCR or both)"; 	
where ((ab_colldate_new lt '01SEP2020'd and ab_colldate_new ne .) or (neg_colldate lt "01SEP2020"d and neg_colldate ne . and neg_test = 'AB')) ;
run;