topic PROC REPORT in ODS and Base Reporting

PROC REPORT

Daily1 — Fri, 29 Apr 2022 06:58:51 GMT

HOW TO WRITE PROC REPORT CODE FOR THIS TABLE

proc report data=WORK;
column A B C (("D" F G) G
("H" I ('J' K L M) N);

run;

Re: PROC REPORT

Kurt_Bremser — Fri, 29 Apr 2022 07:49:49 GMT

Please supply usable example data for us to play around with.

(Usable = data step with datalines)

I take it that D, H and J should be literal strings used as headers?

Re: PROC REPORT

Daily1 — Fri, 29 Apr 2022 10:15:30 GMT

dataline;
A B C E F G I K L M N
1 japan 190 46 15 0 0 0 0 0 1
2 us 152 39 47 86 0 0 0 0 1
3 aus 50 6 36 41 0 0 0 0 1

D, H and J is strings used as headers

Re: PROC REPORT

Kurt_Bremser — Fri, 29 Apr 2022 10:46:33 GMT

This is what we mean by "data step with datalines":

data have;
input A B $ C E F G I K L M N;
datalines;
1 japan 190 46 15 0 0 0 0 0 1
2 us 152 39 47 86 0 0 0 0 1
3 aus 50 6 36 41 0 0 0 0 1
;

It is not rocket science, so you can do it.

The nearest thing I can come up with is this:

proc report data=have;
column ("A" ("" A)) ("B" ("" B)) ("C" ("" C)) ("D" (("E" E) ("F" F))) ("G" ("" G))
("H" ("I" I) ('J' K L M) ("N" N));
define A / "" display;
define B / "" display;
define C / "" display;
define E / "" display;
define F / "" display;
define G / "" display;
define I / "" display;
define K / display;
define L / display;
define M / display;
define N / "" display;
run;

I guess @Cynthia_sas can come up with something that also does the alignment and the border lines.

Re: PROC REPORT

Daily1 — Fri, 29 Apr 2022 10:54:34 GMT

i want this type

Re: PROC REPORT

Cynthia_sas — Fri, 29 Apr 2022 21:28:12 GMT

Thanks for your faith in me @Kurt_Bremser but this is harder to adjust than you'd think. In Excel, you can achieve row spanning and column spanning fairly easily which is what the desired picture of the headers looks like something from Excel. The challenge with that is that PROC REPORT does 1 row at a time on the report. The first header row, then the next header row, then however many header rows, then the data rows. But, here's the challenge, when the first header row is placed on the report, it can't be changed. So when the second header row might have nested spanning headers, PROC REPORT will NOT go back and adjust any of the headers in the first row that might look better if they were adjusted. So, while this might be possible to achieve using some helper variables, as shown in this paper https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf on pages 11-13 -- it's not exactly the same scenario, but the concept is somewhat similar.

It might also be easier to accomplish using the row spanning and column spanning capabilities of the Report Writing Interface. In doing some preliminary analysis, since I have limited time today, this is what I came up with. It appears to me that the headers have both horizontal and vertical spanning that is desired, as shown below:

But PROC REPORT isn't good at this (vertical and horizontal spanning). I suspect that either the column spanning will work, but then some headers might appear on the wrong rows. One issue is that there will be column borders that won't match what is desired. And remember that PROC REPORT is writing 3 distinct report header rows so it's a lot of fiddly work to get it so everything spans correctly. PROC REPORT is NOT going to truly "merge across" or "merge down" the way that Excel would do.

This is the best focus I can devote to this now. Just looking at it, I think the in the end, PROC REPORT isn't going to be able to do the job and this might be something that needs to be done with the Report Writing Interface. If I have time I can try to poke around and see whether I can make it work in PROC REPORT, but I have a hunch that in the end it won't be what is desired.

Cynthia

PS -- had a chance to test out my theory with helper variables to make "fake" header columns. Thanks Kurt for the code to use as a starter. Here's the best that I think can be achieved with PROC REPORT without a LOT more fiddling and I'm not even sure the fiddling would work:

But even with the fake helper variables as ACROSS items, you can see that the row 1 spacing and row 2 spacing vertically is not what was depicted by the original poster.

PROC REPORT

Daily1 — Sun, 01 May 2022 10:05:45 GMT

how to create this type of table using proc report

Re: PROC REPORT

PaigeMiller — Sun, 01 May 2022 10:46:59 GMT

Please provide (a portion of) the data as SAS data step code, which you can type in yourself or use this macro, and not in any other form.

@Daily1 you have previously been asked to provide data in a usable form, please do that from now on, without making us ask each time.

Re: PROC REPORT

Kurt_Bremser — Sun, 01 May 2022 11:00:27 GMT

I moved your post into here, where you already asked a similar question. Please provide data following my example.