How Can I Output Result as Desired Order in RTF?

[KentaMURANAKA]

Hello, there:

I am creating RTF output and stuck in one problem.

I want to output in the order below.

(Result Body) -> (One Blank Row between Solid Line) -> (Footnote)

My code is below, and I do not know where problem is.

Before running PROC REPORT, I sorted by my desired order, but PROC REPORT's output (I used OUT= option in PROC REPORT) seems not to be sorted (One blank row located in strange position, not after Result Body).

Any advice is welcome. Thank you in advance.

data test;
    input sort1 cohort$ sort2 subjid$ value$;
    datalines;
    1 A 1 A-01 xx
    1 A 2 A-02 xx
    2 B 3 B-01 xx
    2 B 4 B-02 xx
    2 B 5 B-03 xx
    ;
run;
proc sort data=test;
    by sort1 sort2;
run;
data test_1;
    set test;
    by sort1 sort2;


    if mod(_n_-1, 12) eq 0 then page+1;
run;
proc sort data=test_1;
    by page sort1 sort2;
run;
data final;
    set test_1;
    by page sort1 sort2;


    output;


    if last.page eq 1 then do;
        call missing(of cohort subjid value);
        flg_line=1;
        output;
    end;
run;
ods rtf file="Please Specify Your Own Folder\test.rtf";
proc report data=final missing out=data
    style(header)={backgroundcolor=white}
    style(report)={rules=groups frame=above}
    ;
    column page sort1 cohort sort2 subjid value flg_line;
    define page / order order=internal noprint;
    define sort1 / order order=internal noprint;
    define cohort / order "COHORT";
    define sort2 / order order=internal noprint;
    define subjid / display "SUBJECT";
    define value / display "VALUE";
    define flg_line / order order=internal noprint;
    break after page / page;
    compute after _page_;
        line "footnote";
    endcomp;
    compute flg_line;
        if flg_line eq 1 then do;
            call define(_row_, "style", "style(calldef)={bordertopcolor=black bordertopstyle=solid bordertopwidth=0.5pt
                borderbottomcolor=black borderbottomstyle=solid borderbottomwidth=0.5pt}");
        end;
    endcomp;
run;
quit;
ods rtf close;

[ChrisNZ · Accepted solution]

This works:

data FINAL;
    set TEST_1;
    by PAGE SORT1 SORT2;
    output;
    if last.PAGE then do;
        call missing(COHORT, SUBJID, VALUE);
        FLG_LINE=1; 
        SORT2+1;       * <=== Added this line  ;
        output;
    end;
run;
ods rtf file="%Sysfunc(pathname(WORK))\test.rtf";
proc report data=FINAL missing out=DATA
    style(header)={backgroundcolor=white}
    style(report)={rules=groups frame=above} ;
  column PAGE SORT2 COHORT  SUBJID VALUE FLG_LINE;
  define PAGE    / order order=internal noprint;
  define SORT2   / order order=internal noprint;
  define FLG_LINE/ order order=internal noprint;
  define COHORT  / order "COHORT";
  define SUBJID  / display "SUBJECT";
  define VALUE   / display "VALUE";
  break after page /;
  compute after _page_;
    line "footnote";
  endcomp;
  compute flg_line;
    if flg_line eq 1 then  
      call define(_row_, "style", "style(calldef)={bordertopcolor=black bordertopstyle=solid bordertopwidth=0.5pt
                                       borderbottomcolor=black borderbottomstyle=solid borderbottomwidth=0.5pt}");
  endcomp;
run;
ods rtf close;

[KentaMURANAKA]

Hi, ChrisNZ-san:

Thank you for reply.

Your idea seems to be good, but you also changed code in PROC REPORT, right?

if there are duplicate values in Variable COHORT, I want to display only first unique value, so I write code like what I inserted.

In my previous code, I had already tried your idea, but did not work.

I have no idea, please help me.

[ChrisNZ]

> you also changed code in PROC REPORT, right?

Of course since it didn't do what you wanted

I don't think formatting should be done by changing the data, but this kludge works:

data FINAL;
    set TEST_1;
    by PAGE SORT1 SORT2;
    output;
    if last.PAGE then do;
        call missing( SUBJID, VALUE);
        FLG_LINE=1; 
        SORT2+1;      COHORT='Z'; 
        output;
    end;
run;
ods rtf file="%Sysfunc(pathname(WORK))\test.rtf";
proc report data=FINAL missing out=DATA
    style(header)={backgroundcolor=white}
    style(report)={rules=groups frame=above} ;
  column PAGE COHORT SUBJID VALUE ;
  define PAGE    / order order=internal noprint;
  define COHORT  / group;
  define SUBJID  / group;
  define VALUE   / group;
  break after page / ; 
  compute after _page_;
    line "footnote";
  endcomp;
  compute cohort;
    if _C2_='Z' then call define(_row_, "style", "style={bordertopcolor=black bordertopstyle=solid bordertopwidth=0.5pt
                                                  color=white
                                                  borderbottomcolor=black borderbottomstyle=solid borderbottomwidth=0.5pt}");
  endcomp;
run;
ods rtf close;

[KentaMURANAKA]

Hi, ChrisNZ-san:

Really thank you for your cooperation.

I understand your trick, COHORT=Z displayed, but seems to disappear by using COLOR=WHITE.

It looks okay, but are there any other better resolutions?

If possible, I want to resolve this problem by not using COLOR=WHITE.

And if anyone know why this problem happened in my code, please let me know.

Thank you in advance.

[KentaMURANAKA]

Thank you, ChrisNZ-san.

Thank to your hint, I added SORT1+1 in my code, and I accomplished my objective.

I think (first variable for sorting+1) is important.

But I do not understand why.

Anyway, thanks!!

data final;
    set test_1;
    by page sort1 sort2;


    output;


    if last.page eq 1 then do;
        call missing(of cohort subjid value);
        sort1+1;          ************ Added!! ************;
        flg_line=1;
        output;
    end;
run;

[ballardw]

---

@KentaMURANAKA wrote:

Hi, ChrisNZ-san:

Thank you for reply.

Your idea seems to be good, but you also changed code in PROC REPORT, right?

if there are duplicate values in Variable COHORT, I want to display only first unique value, so I write code like what I inserted.

In my previous code, I had already tried your idea, but did not work.

I have no idea, please help me.

---

You should show explicitly what your expected output would be. Such a create the table manually in the word processor with the values we should get from the example data set. Then there is much less question about what is expected.

[KentaMURANAKA]

Hi, ballardw-san:

Thank you for reply.

I think, in this case, my 1st uploaded code can express all I expected and questioned, so attached nothing supplemental.

But also think, less information and I should have give you more information.

Thank you.