topic Re: proc report table in SAS Programming

proc report table

PrinceAde — Fri, 13 Oct 2023 08:25:55 GMT

Hi everyone;

I have asked this question before, I'm asking again to be sure people understand the question. Please pardon me.

The computed line after agegrp is preventing the vjust=center from moving the text to the center, more like the computed line " " is a barrier, you can run the code without the compute block to see the difference.
Thanks.

data have;

input strain $ day cyto $ age $;

datalines;

wuhan 1 CD107a 18

wuhan 1 IFN? 18

wuhan 1 TNFa 18

wuhan 1 L-12 18

wuhan 1 CD107a 40

wuhan 1 IFN? 40

wuhan 1 TNFa 40

wuhan 1 L-12 40

wuhan 64 CD107a 18

wuhan 64 IFN? 18

wuhan 64 TNFa 18

wuhan 64 L-12 18

wuhan 64 CD107a 40

wuhan 64 IFN? 40

wuhan 64 TNFa 40

wuhan 64 L-12 40

wuhan 120 CD107a 18

wuhan 120 IFN? 18

wuhan 120 TNFa 18

wuhan 120 L-12 18

wuhan 120 CD107a 40

wuhan 120 IFN? 40

wuhan 120 TNFa 40

wuhan 120 L-12 40

wuhan 360 CD107a 18

wuhan 360 IFN? 18

wuhan 360 TNFa 18

wuhan 360 L-12 18

wuhan 360 CD107a 40

wuhan 360 IFN? 40

wuhan 360 TNFa 40

wuhan 360 L-12 40

;

run;

data want;
set have;
if age= "18" then agegrp = "18-39";
else if age = "40" then agegrp = "40-60";

drop age;
run;

ods pdf file="C:\Users\Classroom\Desktop\internship\practice.pdf";

proc report data=want spanrows headskip nowd headline ps=170 split='*' missing contents="";

column strain day agegrp Cyto;

define strain / style={just=c asis=on cellwidth=6% vjust=center} order flow;

define day / style={just=c asis=on cellwidth=7% vjust=center} "Day" order flow;

define agegrp / style={just=c asis=on cellwidth=9% vjust=center} "Age Group" order flow;

define Cyto / style={just=c asis=on cellwidth=7%} "Cytokine" display flow;

by strain;

compute after agegrp;

line " ";

endcomp;

compute after strain;

line "...";

endcomp;

run;

ods pdf close;

Re: proc report table

Kurt_Bremser — Fri, 13 Oct 2023 09:42:21 GMT

Please add a link to the previously posted question, so we can see what was already suggested.

Re: proc report table

PrinceAde — Fri, 13 Oct 2023 09:49:26 GMT

Hi @Kurt_Bremser

I can't access that again, the conversation was archived/deleted. But spanrows and vjust=center were suggested, that's how I got the current table.

Thank you.

Re: proc report table

Oligolas — Fri, 13 Oct 2023 10:32:22 GMT

Hi,

so you want do insert an empty line after each age group but still want to group by strain and merge the cells vertically.

It should be doable within proc report but I would have to extensively consult the documentation and try around

In case you do not get any suitable answer here, you could do a quick and dirty work-around by editing your dataset before printing and inserting the empty row/cells you need:


proc sort data=want out=want1;by strain day agegrp data; run;

data want2;
   set want1;
   by strain day agegrp;
   retain agegrpcat .;
   if first.agegrp then agegrpcat+1;
run;

data want3;
   set want2;
   by strain day agegrp agegrpcat;
   output;

   if last.agegrpcat then do;

      ARRAY chars _CHARACTER_;
      do over chars;
         if strip(upcase(vname(chars))) not in ('STRAIN' 'AGEGRPCAT') then do;
            call missing(chars);
         end;
      end;

      ARRAY nums _NUMERIC_;
      do over nums;
         if strip(upcase(vname(nums))) not in ('STRAIN' 'AGEGRPCAT') then do;
            call missing(nums);
         end;
      end;
      output;
   end;

run;

ods pdf file="C:\Users\Classroom\Desktop\internship\practice2.pdf";
proc report data=want3 spanrows headskip nowd headline ps=170 split='*' missing contents="";
   column strain day agegrp agegrpcat Cyto;
    define strain    / style={just=c asis=on cellwidth=6% vjust=center} order            flow;
    define day       / style={just=c asis=on cellwidth=7% vjust=center} "Day"       flow;
    define agegrp    / style={just=c asis=on cellwidth=9% vjust=center} "Age Group" flow;
    define agegrpcat / noprint ;
    define Cyto      / style={just=c asis=on cellwidth=7%} "Cytokine" display flow;
run;
ods pdf close;

Re: proc report table

PrinceAde — Fri, 13 Oct 2023 11:27:25 GMT

This empty blank is above the values.

Re: proc report table

ballardw — Fri, 13 Oct 2023 15:32:04 GMT

Going to repeat some stuff from the "archived": You are using options that only work in the LISTING destination. See the highlighted bits below in red. When you include those it causes questions about exactly what you think is contributing to the specific report. If you show a picture from the LISTING destination then it will not match any other ODS destination because the other destinations won't use these.

ods pdf file="C:\Users\Classroom\Desktop\internship\practice.pdf";
proc report data=want spanrows headskip nowd headline ps=170 split='*' missing contents="";
   column strain day agegrp Cyto;
    define strain / style={just=c asis=on cellwidth=6% vjust=center} order flow;
    define day / style={just=c asis=on cellwidth=7% vjust=center} "Day" order flow;
    define agegrp / style={just=c asis=on cellwidth=9% vjust=center} "Age Group" order flow;
    define Cyto / style={just=c asis=on cellwidth=7%} "Cytokine" display flow;
by strain;
compute after agegrp;
line " ";
endcomp;
compute after strain;
line "...";
endcomp;
run;

I also call out that "picture" of your output does not come from the report code above. Reason: your picture does not show any result of the line statment from the compute after for agegrp and strain.

With your example data there is no "missing" as shown in that picture.

There are times for proc report that you add helper variable(s) that do not actually display in the body of the report.

You place them on the columns statement to the LEFT so they are available to control just about any appearance in the actual body. The on the DEFINE you use option NOPRINT which tells SAS to include them in the body of the report.

Then you can use Compute blocks with THOSE variable to change appearance based on the values.

Re: proc report table

Cynthia_sas — Fri, 13 Oct 2023 16:04:12 GMT

Hi:

Using your posted data and simplifying your code and changing the compute block for the blank line and removing the unneeded cellwidth and asis=on specifications and reducing the output to fit on one page, I think this is closer to what you want. I'm still not sure of the purpose or reason behind the need to show 3 dots however, I have highlighted the lines created by changing compute after agegrp to compute after day and then I've also highlighted the one line that was written by compute after strain:

SPANROWS seems to be working correctly .

Cynthia

Re: proc report table

PrinceAde — Fri, 13 Oct 2023 17:32:41 GMT

Hi @Cynthia_sas Thanks.

The computed blank is after agegp and not day.

If you use agegp(compute after agegp; line " "; run; endcomp;), you will notice that the text(e.g Wuhan) won't move pass the blank line. I'm trying to move the text beyond that line(for example, wuhan should be somewhere between 64 and 120 in spite of the blank line).

Re: proc report table

Cynthia_sas — Fri, 13 Oct 2023 17:32:47 GMT

Hi:

That's not the way that PROC REPORT works by default. When you have a group or order variable as primary (as you do with strain) and then you have another group or order variable nested within that primary order variable, the values for the primary ordering variable do NOT automatically repeat for each group. You need to control for that yourself, either by making a temporary computed variable that you create. The way that ORDER items work is that the duplicate values are suppressed. So if you really want a blank link under each agegroup unique value and you want the values of strain to repeat, then you'd need to do something more like this:

And then, if you wanted to do something similar for the DAY variable, you'd use similar logic. In my example SAVESTRAIN is a PROC REPORT temporary variable whose value is retained automatically. So at the start of every unique value for STRAIN, I "grab" and save the current value in a variable so I can use the value as shown in the COMPUTE blocks.

Cynthia

Re: proc report table

PrinceAde — Fri, 13 Oct 2023 17:48:32 GMT

@Cynthia_sas I'm not trying to repeat the values of strain, I'm to move the text to the middle. For example, the agegp is well placed between the cytokines, days should be between 18-39 and 40-60, likewise Wuhan should be in the middle of the value of day(1, 64, 120 and 360, it should be between 64 and 120, in spite of the blank line), please note that without the blank line after agegp, it works fine in positioning these values in the middle.

Thanks.

Re: proc report table

Cynthia_sas — Fri, 13 Oct 2023 19:35:07 GMT

Hi: Unfortunately, I'm not sure I understand what you mean. Saying that a value "should" be in the middle of the another value is expressing your desired result. PROC REPORT may not do what you think it "should" or what you want it to do.
PROC REPORT considers a break line placed with a COMPUTE block to be a "hard" break. So when you say that a value "should" be positioned "in spite of the blank line" means you want PROC REPORT to do something that it does NOT do. That's not how PROC REPORT operates. You can control the placement of the break line, as I showed when I shifted the break from AGEGRP to DAY. And, shifting the break caused the spanning text for the ORDER items to be shifted accordingly. But the placement of the order variables when using SPANROWS very much depends on the placement of the break line, so the "should" of what you want may or may not be possible given the way that PROC REPORT works.
The example below shows the way that PROC REPORT is going to work based on different positioning of the line written in the COMPUTE block.

This is how break processing works. When you write a LINE with a COMPUTE block, it will span the entire report table from left side to right side, thus changing the placement of STRAIN and DAY, as shown in my 3 examples above.

This IS the way PROC REPORT will work. What you say it "should" do or what you want it to do is not possible if you continue to put your LINE statement blank line between each AGEGRP value. You may need to investigate the Report Writing Interface with a DATA step program if you need more control over row spanning and column spanning.
Cynthia

Re: proc report table

ballardw — Fri, 13 Oct 2023 21:04:55 GMT

Suggestion: instead of showing things that are not what you actually want show what you do want. You haven't been very clear on that in any of the threads related to this.

Use the example.

Go to a word processor program and start working with the table options. Place the values in the desired locations with the cell borders.

Instead of a blank or series of blanks you may want to show something like <blank line goes here> because there could very well be other places with blank cells and we can't tell clearly which is supposed to be from what.

No guarantees you will get a Proc Report answer.

Re: proc report table

PrinceAde — Sat, 14 Oct 2023 14:28:12 GMT

@ballardw @Cynthia_sas

Sorry for the late reply. I have the snippet of the table I expect to get.

Thanks.

Re: proc report table

ballardw — Sat, 14 Oct 2023 22:02:52 GMT

The desire to have the Strain "span" across values of days and age means that a Report LINE statement isn't going to work. That goes across the entire width of a report table, which is going interfere with spanning rows.

Summarized you values so proc report is not having to summarize anything.

Then look at the DATA step Report Writing Interface that allows you to mix spanning rows, columns based on the values of the variable conditionally.

Re: proc report table

andreas_lds — Mon, 16 Oct 2023 06:05:12 GMT

I would use RWI to create a report with so many special layout requirements.

Re: proc report table

Kurt_Bremser — Mon, 16 Oct 2023 07:29:27 GMT

With complex layout requirements, I tend to use HTML, and use a DATA step to write the raw HTML code.

First, I write a HTML file manually until I get the wanted result, and then I automate that creation.

HTML files also make the publishing part easy, just put them into the documents directory of the SAS BI web server.

Re: proc report table

Oligolas — Mon, 16 Oct 2023 08:29:27 GMT

data have;
input strain $ day cyto $ age $;
datalines;
wuhan 1 CD107a 18
wuhan 1 IFN? 18
wuhan 1 TNFa 18
wuhan 1 L-12 18
wuhan 1 CD107a 40
wuhan 1 IFN? 40
wuhan 1 TNFa 40
wuhan 1 L-12 40
wuhan 64 CD107a 18
wuhan 64 IFN? 18
wuhan 64 TNFa 18
wuhan 64 L-12 18
wuhan 64 CD107a 40
wuhan 64 IFN? 40 
wuhan 64 TNFa 40
wuhan 64 L-12 40
wuhan 120 CD107a 18
wuhan 120 IFN? 18
wuhan 120 TNFa 18
wuhan 120 L-12 18
wuhan 120 CD107a 40
wuhan 120 IFN? 40
wuhan 120 TNFa 40
wuhan 120 L-12 40
wuhan 360 CD107a 18
wuhan 360 IFN? 18
wuhan 360 TNFa 18
wuhan 360 L-12 18
wuhan 360 CD107a 40
wuhan 360 IFN? 40
wuhan 360 TNFa 40
wuhan 360 L-12 40
;
run;
data want;
set have;
if age= "18" then agegrp = "18-39";
else if age = "40" then agegrp = "40-60";
drop age;
run;

proc sort data=want out=want1;by strain day agegrp; run;

data want2;
   set want1;
   by strain day agegrp;
   retain agegrpcat .;
   if first.agegrp then agegrpcat+1;
run;

data want3;
   set want2(rename=(day=_day));
   by strain _day agegrp agegrpcat;
   output;

   if last.agegrpcat then do;

      ARRAY chars _CHARACTER_;
      do over chars;
         if strip(upcase(vname(chars))) not in ('STRAIN' 'AGEGRPCAT' '_DAY') then do;
            call missing(chars);
         end;
      end;

      ARRAY nums _NUMERIC_;
      do over nums;
         if strip(upcase(vname(nums))) not in ('STRAIN' 'AGEGRPCAT'  '_DAY') then do;
            call missing(nums);
         end;
      end;
      day=_day;
      output;
   end;
   drop _day;
run;

ods pdf file="C:\Users\Classroom\Desktop\internship\practice.pdf";
proc report data=want3 spanrows headskip nowd headline ps=170 split='*' missing contents="";
   column strain day agegrp agegrpcat Cyto;
    define strain    / style={just=c asis=on cellwidth=6% vjust=center} order            flow;
    define day       / style={just=c asis=on cellwidth=7% vjust=center} "Day"       flow;
    define agegrp    / style={just=c asis=on cellwidth=9% vjust=center} "Age Group" flow;
    define agegrpcat / noprint ;
    define Cyto      / style={just=c asis=on cellwidth=7%} "Cytokine" display flow;
run;
ods pdf close;