How to make a subgroup instead of a separate column in PROC REPORT?

[FriendJen]

If I have two variables, where one is a subgroup of another, how can I make the ODS RTF and ODS PDF output report look like the picture I have below with Type indented under Make instead of having them be two separate columns? 

Edited for clarifications.

[Kurt_Bremser]

Please supply your source data in usable form, as you were shown here: https://communities.sas.com/t5/Statistical-Procedures/How-to-use-PROC-MIANALYZE-with-PROC-PHREG-for-... 

Use the "little running man" button right next to the one indicated for posting the code:

(the indicated button is for logs and other text where you want to keep the fixed-space formatting)

[FriendJen]

Thank you for responding! Unfortunately, I cannot post the source code for my data as it contains sensitive information. The picture I posted was from the example table I am trying to make (after I changed the variable names).

Let's say the data is the sashelp.cars dataset, with the variable Make as Region, Type as Country, DriveTrain as a group variable for counts columns. Counts will be the number of models falling under that category.

Sorry for the confusion, I will update the question to reflect this.

[FreelanceReinh]

Hello @FriendJen,

One way to achieve this is to create a new character variable, say reg_ctry, which contains the values for the first column. You can still use the original variable Region as an ORDER variable.

Here's an example using SASHELP.CARS (and its variable Make as "country"):

proc summary data=sashelp.cars(rename=(Origin=Region Make=Country));
class region country;
output out=counts(where=(_type_ ne 1));
run;

proc sort data=counts;
by region;
run;

data want(keep=r: n:);
set counts;
length reg_ctry $20 n_pct $13;
if _type_=0 then do;
  reg_ctry='All';
  _t=_freq_;
  call symputx('_t',_t);
  link calc;
end;
else if _type_=2 then do;
  reg_ctry=region;
  n_pct=' ';
  output;
  reg_ctry='  Any country';
  link calc;
end;
else do;
  reg_ctry='  '||country;
  link calc;
end;
retain _t;
return;
calc:
  _pct=_freq_/_t;
  n_pct=put(_freq_,4.)||' '||put(-_pct,percent8.1);
  output;
return;
run;

proc report data=want headline;
column region reg_ctry n_pct;
define region / order missing noprint;
define reg_ctry / 'Region/   Country';
define n_pct / "Count/(N=&_t)";
break before region / skip;
compute after;
  line @3 35*'_';
endcomp;
run;

Result (using the ODS listing destination):

  Region                Count
    Country             (N=428)
  ___________________________________

  All                    428 (100.0%)

  Asia
    Any country          158 ( 36.9%)
    Acura                  7 (  1.6%)
    Honda                 17 (  4.0%)
    Hyundai               12 (  2.8%)
    Infiniti               8 (  1.9%)
    Isuzu                  2 (  0.5%)
    Kia                   11 (  2.6%)
    Lexus                 11 (  2.6%)
    Mazda                 11 (  2.6%)
    Mitsubishi            13 (  3.0%)
    Nissan                17 (  4.0%)
    Scion                  2 (  0.5%)
    Subaru                11 (  2.6%)
    Suzuki                 8 (  1.9%)
    Toyota                28 (  6.5%)

  Europe
    Any country          123 ( 28.7%)
    Audi                  19 (  4.4%)
    BMW                   20 (  4.7%)
    Jaguar                12 (  2.8%)
    Land Rover             3 (  0.7%)
    MINI                   2 (  0.5%)
    Mercedes-Benz         26 (  6.1%)
    Porsche                7 (  1.6%)
    Saab                   7 (  1.6%)
    Volkswagen            15 (  3.5%)
    Volvo                 12 (  2.8%)

  USA
    Any country          147 ( 34.3%)
    Buick                  9 (  2.1%)
    Cadillac               8 (  1.9%)
    Chevrolet             27 (  6.3%)
    Chrysler              15 (  3.5%)
    Dodge                 13 (  3.0%)
    Ford                  23 (  5.4%)
    GMC                    8 (  1.9%)
    Hummer                 1 (  0.2%)
    Jeep                   3 (  0.7%)
    Lincoln                9 (  2.1%)
    Mercury                9 (  2.1%)
    Oldsmobile             3 (  0.7%)
    Pontiac               11 (  2.6%)
    Saturn                 8 (  1.9%)
  ___________________________________

[FriendJen]

Thank you for your response!! Do you know how I can do this with ODS RTF and ODS PDF output?
(I've updated my post to clarify)

[FreelanceReinh]

---

@FriendJen wrote:

Do you know how I can do this with ODS RTF and ODS PDF output?
(I've updated my post to clarify)

---

For simplicity, please let me continue with my original example.

For ODS PDF I found useful tips in section 8.6.5 "Line Breaks and Wrapping" of Carpenter's Complete Guide to the SAS® REPORT Procedure, p. 282 ff. I inserted empty observations into dataset WANT (to obtain the blank lines) and replaced the leading blanks in variable reg_ctry by ~_ (where '~' will serve as the ODS escape character). However, see a variant of this, suitable for both PDF and RTF, further below.

data want(keep=r: n:);
set counts end=eof;
by region;
length reg_ctry $20 n_pct $13;
if _type_=0 then do;
  output;
  reg_ctry='All';
  _t=_freq_;
  call symputx('_t',_t);
  link calc;
end;
else if _type_=2 then do;
  reg_ctry=region;
  n_pct=' ';
  output;
  reg_ctry='~_~_Any country';
  link calc;
end;
else do;
  reg_ctry='~_~_'||country;
  link calc;
end;
if last.region and not eof then do; 
  call missing(reg_ctry, n_pct);
  output;
end;
retain _t;
return;
calc:
  _pct=_freq_/_t;
  n_pct=put(_freq_,4.)||' '||put(-_pct,percent8.1);
  output;
return;
run;

The escape-underscore sequences (creating non-breaking spaces) are also used in the PROC REPORT code controlling the headers, together with additional in-line commands (m=indentation mark, 1n=line break, -2n=line break honoring the indentation mark) and style specifications.

ods pdf style=monospace file='C:\Temp\want.pdf';
ods escapechar = '~';

proc report data=want style(hdr)={just=left};
column region reg_ctry n_pct;
define region / order missing noprint;
define reg_ctry / 'Region~1n~_~_Country';
define n_pct / "~_~_~_~_~_~mCount~-2n(N=&_t)" style(column)={just=right};
compute after;
  line 35*'_';
endcomp;
run;

ods pdf close;

With ODS RTF the "~_" sequence did not produce the desired indentation. Therefore I replaced it with hex A0 characters (=non-breaking spaces). That is, in the DATA step shown above only two lines are modified:

data want(keep=r: n:);
...
  reg_ctry='A0A0'x||'Any country';
...
  reg_ctry='A0A0'x||country;
...
run;

As it turned out, these work with ODS PDF as well, so you can use this WANT dataset for both ODS destinations.

In the header strings specified in the DEFINE statements of the PROC REPORT step I typed the 'A0'x characters as Alt+0160. (Note that hexadecimal A0 = decimal 160.)

ods rtf style=monospace file='C:\Temp\want.rtf';
ods escapechar = '~';

proc report data=want style(hdr)={just=left};
column region reg_ctry n_pct;
define region / order missing noprint;
define reg_ctry / 'Region~1n  Country'; /* Blanks are 'A0'x characters! */
define n_pct / "     ~mCount~1n     (N=&_t)" /* Blanks are 'A0'x characters! */
               style(column)={just=right};
compute after;
  line 35*'_';
endcomp;
run;

ods rtf close;

Results:

left: PDF, right: RTF

[Cynthia_sas · Accepted solution]

Hi: 

  For RTF and PDF destinations, I find that the leftmargin style attribute works for me. I had this example that I show when students ask about indenting a subgroup under a group:

  The leftmargin attribute won't work for the LISTING destination, but it works for me for RTF and PDF. A possible alternative to consider.

Cynthia

[FreelanceReinh]

Thank you so much, @Cynthia_sas, for chiming in! Leftmargin style attribute -- this looks much more professional than my SAS-v6-inspired way of pushing strings around with 'A0'x characters. Indeed a great alternative to consider. I also like the clever use of the COMPUTE block. And the fact that the same PROC REPORT step works equally well for both ODS destinations.

Looks like I should enroll in one of your ODS classes ...

[Cynthia_sas]

Thanks @FreelanceReinhard!
When you couple the leftmargin style override with the STYLE=JOURNAL look and feel of the output, it works very nicely for these type of "demographic" reports. In fact, I first used the leftmargin style attribute in the examples for an SGF paper back in '08 (2008, that is). Here's the link: https://support.sas.com/resources/papers/proceedings/pdfs/sgf2008/173-2008.pdf and then a follow-up to that paper in 2016 called That's All Right: More Complex Reports, here https://support.sas.com/resources/papers/proceedings16/SAS5762-2016.pdf (and just for grins, the section titles are riffs on Elvis songs ).
Cynthia