Proc Report Tabular Alignment

[sanalitics]

Below is the simple proc report query, Attached is the output of the query and what is further expected from the proc report for a standard reporting format

data test;

Name="sas";

Rank=1;

loc="a";

run;

proc report data=test;

column ("test" "test1" Name rank ) ("test" loc);

run;

Kindly assist with the appropriate proc report option to achieve the required reporting format.

[Reeza]

Looks like it may not have been possible a couple of years ago via Proc Report, but is via proc tabulate.

Depends on your data and actual report I suppose.

Link to old post:

[sanalitics]

Dear Reeza,

Thank you for the response, But a solution using proc report is what was expected as tabulate is mostly for the classification and

aggregations.

Considering there are no numeric variables and only text variables, Proc report would keep things simple with options to add our customized title and create a table kind of layout...

let us take an example:

data test;

input name1 $ name2 $ name3 $ name4 $ ;

cards;

A1 A2 A3 A4

A11 A22 A33 A44

;

run;

proc report data=test;

column ("Head 1" name1 ("Head11" name2)) name3 name4;

run;

Attached is the actual output and expected output ... let me know if you could help with relevant solution using any procedure.

Thanks again for all your efforts on investigating.

[Cynthia_sas]

Hi:

  PROC REPORT writes every "Header" row separately from every other header row. So it will not "span" header rows as you show in your desired/expected output. Your "Head1" is making a separate header row. So if you do not put any text above name3 and name4 at the same level as Head1, then you will get an empty cell on header row 1. Then you have header row 2, with Head11 above Name2. Again, you have designed your headers so that Head11 needs a whole row. Just as there is an empty cell above name1's header, there is an empty space in the header row above name3 and name 4. Then you have your final header row with the headers name1, name2, name3 and name4 -- at the point in time when PROC REPORT is writing the last header row, it can't "go back" and change the rows about name3 and name4 to spanning headers such as you show. PROC REPORT writes every header row a single row at a time, so one row has to finish before the next row starts. That is why your Actual output is what you should expect -- PROC REPORT is working as it is supposed to work. Your desired output with a spanning row header above name3 and another spanning row header above name 4 is not do-able with PROC REPORT.

cynthia

[sanalitics]

Cynthia , Do we have any other methods/procedures to produces the desired output for the above mentioned example?

[Cynthia_sas]

Hi: Not really an "out of te box" solution. There's always post processing the report file.  If your destination is ODS RTF, then you could edit the headers in Word after the RTF file is created to merge table cells. If your destination is ODS HTML, then you could post process the HTML to make the spanning headers.

Worrying about this type of cosmetics seems overly focused on cosmetics to me. SAS is not Excel or Word. You can't merge cells just by highlighting them and selecting a menu option to merge cells. When you are writing reports you have the capabilities of each procedure's method of writing the output report. Sometimes you can change the default method to do what you want, sometimes you can't. Sometimes, you have to adjust your expectations.

The only other thing I can think of is a custom table template with custom headers. Sometimes the order that you define the headers in can cause spanning, but it is not always guaranteed. And you would have to learn PROC TEMPLATE syntx and use DATA _NULL_ to call the template. You would have to calculate your own summary lines if you wanted subtotals or grand totals. So it might be do-able, but to even find out whether it's going to work, you have a learning curve. If you are interested in pursuing this approach to see whether it will do what you want, you would need to read about PROC TEMPLATE for TABLE templates used with DATA _NULL_.

cynthia