Using TableN macro, having trouble with "COLBY" column by option

[Wolverine]

I recently started using the TableN macro and I wanted to say thank you to @JeffMeyers for providing such a useful macro! I was able to produce Table 1 for TM_group (which is a binary variable with values 0,1). However, I would now like to create Table 1 for subgroups based on the ADRD_group variable (which is also 0,1). The documentation for the macro indicates the COLBY option can do exactly what I'm looking for, and provides an example output table with Gender and Arm. However, I can't find good example syntax of how to add the COLBY option to the code. I've tried a few variations, but I keep getting errors. Below is my latest version.

%tablen(
  data=have;
  ,by=TM_group
  .colby=ADRD_group
  ,var=DEM_AGE DEM_SEX 
  ,type=2 2
  ,outdoc=C:\Users\r\Desktop\TM vs MA ADRD Table 1(&version.).rtf
  );

[Patrick]

First fix: remove the semicolon after data=have

[Wolverine]

That was just a typo when I changed the data file name to "have". The original code doesn't have a semicolon there.

[ballardw]

As with any error the LOG including the code and all the notes, warnings or errors should be included.

We have no way to tell what may be the problem as we do not have your data set.

The macro includes multiple checks and most of the error messages seem reasonably clear.

[Wolverine]

I was hoping it would be something simple, like move the colby option to a different line. Here is sample data and the log:

data have;
  infile datalines dsd dlm=',' truncover;
  input DEM_AGE DEM_SEX ADRD_group TM_group;
datalines;
3,1,1,0
2,1,1,1
3,2,1,1
3,2,1,1
3,2,1,0
2,2,1,1
2,1,1,1
3,1,1,1
2,1,1,1
3,2,1,0
2,1,1,0
2,2,1,1
3,2,1,0
2,2,0,1
3,2,1,1
3,2,1,1
3,1,1,0
3,2,1,0
2,1,1,0
3,1,1,1
3,2,1,1
3,2,1,0
3,2,1,1
3,2,1,1
3,2,1,1
; RUN;

%tablen(data=have
  ,by=TM_group
  ,colby=ADRD_group
  ,var=DEM_AGE DEM_SEX 
  ,type=2 2
  ,outdoc=C:\Users\r\Desktop\TM vs MA ADRD Table 1(&version.).rtf
  );

16167  %tablen(
ERROR: All positional parameters must precede keyword parameters.
16168    data=have
16169    ,by=TM_group
16170  ,colby ADRD_group
16171    ,var=DEM_AGE DEM_SEX 
          ---
          180
ERROR 180-322: Statement is not valid or it is used out of proper order.
16175    ,type=2 2 
16176    ,outdoc=C:\Users\r\Desktop\TM vs MA ADRD
16176! Table 1(&version.).rtf
16177    );

[ballardw · Accepted solution]

Once again a LOG shows that the code submitted is not what you think it was. From the LOG

16167  %tablen(
ERROR: All positional parameters must precede keyword parameters.
16168    data=have
16169    ,by=TM_group
16170  ,colby ADRD_group
16171    ,var=DEM_AGE DEM_SEX 
          ---

Note COLBY does NOT have the = character that your supposed code shows

%tablen(data=have
  ,by=TM_group
  ,colby=ADRD_group
  ,var=DEM_AGE DEM_SEX 
  ,type=2 2
  ,outdoc=C:\Users\r\Desktop\TM vs MA ADRD Table 1(&version.).rtf
  );

Without the = sign following COLBY then the whole phrase ,colby adrd_group becomes a single positional parameter and would expect to be before the ones shown with =.

[Wolverine]

---

@ballardw wrote:

Once again a LOG shows that the code submitted is not what you think it was. From the LOG

16167  %tablen(
ERROR: All positional parameters must precede keyword parameters.
16168    data=have
16169    ,by=TM_group
16170  ,colby ADRD_group
16171    ,var=DEM_AGE DEM_SEX 
          ---

Note COLBY does NOT have the = character that your supposed code shows

---

Good catch! I must have somehow deleted = before I ran the code. I fixed it and ran it again, and there are no longer any errors in the log.

However, the output is doesn't match the example from the documentation that I seek to reproduce. I want TM_group to be the equivalent of gender in the screenshot below, and ADRD_group to be the equivalent of Treatment Arm.

This is the output I'm actually getting, which has separate tables for each level of ADRD_group

	ADRD_group
	Missing
	TM_group
	0 (N=14)	1 (N=17)	Total (N=31)	P-value
Age group, n (%)				0.95911
2:Age Group [65,75)	4 (28.6%)	5 (29.4%)	9 (29.0%)
3:Age Group >=75	10 (71.4%)	12 (70.6%)	22 (71.0%)
Gender, n (%)				0.66701
1:Male	6 (42.9%)	6 (35.3%)	12 (38.7%)
2:Female	8 (57.1%)	11 (64.7%)	19 (61.3%)
1Chi-Square p-value;

	ADRD_group
	0
	TM_group
	0 (N=21836)	1 (N=29642)	Total (N=51478)	P-value
Age group, n (%)				<.00011
2:Age Group [65,75)	8701 (39.8%)	12504 (42.2%)	21205 (41.2%)
3:Age Group >=75	13135 (60.2%)	17138 (57.8%)	30273 (58.8%)
Gender, n (%)				<.00011
1:Male	9479 (43.4%)	13623 (46.0%)	23102 (44.9%)
2:Female	12357 (56.6%)	16019 (54.0%)	28376 (55.1%)
1Chi-Square p-value;

	ADRD_group
	1
	TM_group
	0 (N=1232)	1 (N=1475)	Total (N=2707)	P-value
Age group, n (%)				0.97241
2:Age Group [65,75)	179 (14.5%)	215 (14.6%)	394 (14.6%)
3:Age Group >=75	1053 (85.5%)	1260 (85.4%)	2313 (85.4%)
Gender, n (%)				0.00591
1:Male	453 (36.8%)	619 (42.0%)	1072 (39.6%)
2:Female	779 (63.2%)	856 (58.0%)	1635 (60.4%)
1Chi-Square p-value;

[Wolverine]

Update: I switched the by and colby variables, and I am now getting the correct output in SAS. However, the output RTF file still has separate tables for each level of the colby variable (which is now TM_group). Here is the latest version of the code:

%tablen(data=have
  ,by=ADRD_group
  ,colby=TM_group
  ,var=DEM_AGE DEM_SEX 
  ,type=2 2
  ,outdoc=C:\Users\r\Desktop\TM vs MA ADRD Table 1(&version.).rtf
  );

[Wolverine]

I have a working theory, which is that the rtf file isn't wide enough to accommodate the additional columns. I need to write the output to Excel instead.

[ballardw]

Depending on how "tall" your output is you might consider the system option  Orientation=Landscape; instead of the default portrait. Set this before the ODS RTF statement.

You can also use OPTIONS PAPERSIZE= before the ODS RTF output is generated to create a wider/taller logical page to display output. I recommend providing a unit such as IN or CM with the option so you know what to expect.

Options papersize=(10in, 8in);

would set the page to 10 inches wide and 8 inches high for example.

The option also takes some paper names but that varies by location and system.