The SAS Output Delivery System and reporting techniques

proc report : trying to use across, group, and display definitions to generate complex report layout

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

proc report : trying to use across, group, and display definitions to generate complex report layout

I am trying to generate a complex report layout using proc report. I have re-created the structure of my PROC REPORT input dataset using a series of do loops and conditional statements below.  I have also provided my proc report code used to generate 3 different approximations of the report I am trying to generate.  The three reports are titled "version 1", "version 2", and "version 3". I will now explain how each version falls short of what I want my final report to look like:

 

"version 1:" This version is very close to what I want my final report to look like except that I do not want var4, var5, var6 to be nested inside the class2 "across" variable.  I am glad however, that the all levels of class1 variable are display which is not the case in "version 3".

 

"version 2:" This version is great in that var4, var5, and var6 are no longer nested in the class2 "across variable" except now the table seems to be displaying a series of cross-classifications for var4, var5, and var6 with the group "noprint" group variable. I do not want these "cross-classifications."

 

"version 3" To eliminate the hypothesized cross-classifications of var4,var5, and var6 with the "noprint" group variable as seen in "version 2", i made them (var4, var5, var6) group variables, but now, this version excludes observations where section=2  and class1 >4.

 

Basically, the question is, what data-step pre-processing or proc-report code needs to be executed to make "version 3" display all levels of the class1 variable?

 

Hope some one can help me.  Many thanks.

 

data top3;
do report=1;
    do section = 1 to 2;
        if section = 1 then do;
            do class1 = 1 to 4;
                do class2 = 4 to 1 by -1;
                    do group = 1 to 2;
                        if group = 1 then do;
                            var1="z";var2="k";var4="z";var5="z";var6="z";
                            if class1 >1 & class2 <4 then var3="s"; else var3=" ";output;
                        end;
                        else do;
                        if group = 2 then do;
                            var1="y";var2=" ";var4="y";var5="y";var6="y";
                            if class1 >1 & class2 <4 then var3="r";else var3=" ";output;
                        end;
                        end;
                        end;
                end;
            end;
        end;
        if section = 2 then do;
            do class1 = 5 to 7;
                do class2 = 4 to 1 by -1;
                        do group = 1 to 2;
                        if group = 1 then do;
                            var1="z";var2=" ";var3=" ";var4=" ";var5=" ";var6=" ";output;
                         end;
                        else do;
                        if group = 2 then do;
                            var1="y";var2=" ";var3=" ";var4=" ";var5=" ";var6=" ";output;
                        end;
                        end;
                    end;
                end;
            end;
        end;
    end;
end;
run;

proc print data=top3;
run;

proc report data=top3 nowd;
title "version 1";
column report section class1 group  class2,(var2 var1 var3 var4 var5 var6)  dummyvar;
define report/group noprint;
define section/group noprint;
define group/group noprint;
define class2/across order=data;
define var2/display width=4;
define var1/display width=4;
define var3/display width=4 nozero;
define var4/display width=4;
define var5/display width=4;
define var6/display width=4;
define class1/group order=data width=6;
define dummyvar/computed noprint;
break before section/skip;
break after section/skip;
compute dummyvar;
    dummyvar=1;
endcomp;
run;

proc report data=top3 nowd;
title "version 2";
column report section class1 group  class2,(var2 var1 var3) (var4 var5 var6)  dummyvar;
define report/group noprint;
define section/group noprint;
define group/group noprint;
define class2/across order=data;
define var2/display width=4;
define var1/display width=4;
define var3/display width=4 nozero;
define var4/display width=4;
define var5/display width=4;
define var6/display width=4;
define class1/group order=data width=6;
define dummyvar/computed noprint;
break before section/skip;
break after section/skip;
compute dummyvar;
    dummyvar=1;
endcomp;
run;

proc report data=top3 nowd;
title "version 3";
column report section class1 group  class2,(var2 var1 var3) (var4 var5 var6)  dummyvar;
define report/group noprint;
define section/group noprint;
define group/group noprint;
define class2/across order=data;
define var2/display width=4;
define var1/display width=4;
define var3/display width=4 nozero;
define var4/group width=4;
define var5/group width=4;
define var6/group width=4;
define class1/group order=data width=6;
define dummyvar/computed noprint;
break before section/skip;
break after section/skip;
compute dummyvar;
    dummyvar=1;
endcomp;
run;


Accepted Solutions
Solution
‎02-10-2018 12:07 PM
SAS Super FREQ
Posts: 9,310

Re: proc report : trying to use across, group, and display definitions to generate complex report la

[ Edited ]

Hi, you do not show ALL your code. What type of output do you want. Default HTML, RTF, PDF, EXCEL . That may make a difference. (One reason I ask is that SKIP is only honored by ODS LISTING so it will NOT be used by RTF, PDF or HTML or EXCEL destinations.)
cynthia

(Looks like my earlier picture did not get posted. Here's the code and the screen shot of the HTML default output)

fix_version_3.png

View solution in original post


All Replies
Solution
‎02-10-2018 12:07 PM
SAS Super FREQ
Posts: 9,310

Re: proc report : trying to use across, group, and display definitions to generate complex report la

[ Edited ]

Hi, you do not show ALL your code. What type of output do you want. Default HTML, RTF, PDF, EXCEL . That may make a difference. (One reason I ask is that SKIP is only honored by ODS LISTING so it will NOT be used by RTF, PDF or HTML or EXCEL destinations.)
cynthia

(Looks like my earlier picture did not get posted. Here's the code and the screen shot of the HTML default output)

fix_version_3.png

Occasional Contributor
Posts: 15

Re: proc report : trying to use across, group, and display definitions to generate complex report la

Posted in reply to Cynthia_sas

Hi Cynthia,

 

Thanks for your reply.

 

My ultimate goal would be to reproduce this report in RTF format. I am learning PROC REPORT and so I've been using the listing format to practice.  So what I provided IS actually ALL of my code. 

 

Since the "skip" option is not honored by RTF, PDF, or HTML, could you tell me how I would need to modify my code to produce similar spacing in these ODS formats?

 

Also, is my question in the original post clear?

 

Thanks.

 

 

SAS Super FREQ
Posts: 9,310

Re: proc report : trying to use across, group, and display definitions to generate complex report la

[ Edited ]

Hi:

Use a COMPUTE block and a LINE statement as I show in my code.
To get ODS RTF output, all you will need to do is "sandwich" the ODS RTF statement around your corrected version3 code, as shown in this new screen shot.

 

Your question was clear -- did you look at my screen shot and suggestion? Your Version 3 code was close, but needs the MISSING option, as shown in my version of your program, which was posted in my response. No pre-processing needs to be done with your data, as you defined the original question.

 

I also showed, in that code, how to use a LINE statement to simulate what SKIP does for the LISTING window. Here is how you get the output into an RTF document.
Cynthia

 

version3_in_rtf.png

Occasional Contributor
Posts: 15

Re: proc report : trying to use across, group, and display definitions to generate complex report la

Posted in reply to Cynthia_sas

Hi Cynthia,

 

Thanks. I'm new to posting on this forum.  I was able to see your screenshot and ods rtf 'sandwich' code but did not see the corrected of my version3.  Where am I supposed to look to see this?

 

In your screenshot, it appears that var3 is displayed at level 4 of the class 2 variable. I haven't been able to see your 'corrected' code, so i'm not sure if the nozero option is still listed?

 

Thanks for your help.

 

 

Occasional Contributor
Posts: 15

Re: proc report : trying to use across, group, and display definitions to generate complex report la

Hi Cynthia,

 

I just saw that your original reply was updated and now includes the code and screenshot. Thanks so much.  I'm so surprised that I was so close! I'm guessing if I included the nozero option for the class2 across variable, then that would suppress the display of var3 for class2=4?

 

 

SAS Super FREQ
Posts: 9,310

Re: proc report : trying to use across, group, and display definitions to generate complex report la

[ Edited ]

Hi:
I got rid of NOZERO for 2 reasons. The purpose of NOZERO is to suppress columns, as described in the doc: http://go.documentation.sas.com/?docsetId=proc&docsetVersion=9.4&docsetTarget=p0wy1vqwvz43uhn1g77eb5...

However, the NOCOMPLETECOLS is what I use to control ACROSS variable behavior whether all the columns under the nesting should be shown or not. I rarely use NOZERO. I just find I don't need it.

cynthia

Here's how I would have changed the PROC REPORT to get rid of the column you have that is all empty:
proc report data=top3 nowd missing nocompletecols;

Occasional Contributor
Posts: 15

Re: proc report : trying to use across, group, and display definitions to generate complex report la

Posted in reply to Cynthia_sas

Excellent.  Thanks for sharing your knowledge.

 

Best wishes.

Occasional Contributor
Posts: 15

Re: proc report : trying to use across, group, and display definitions to generate complex report la

Posted in reply to Cynthia_sas

Just my experience:

 

I will provide these 2 scenarios with the following list items:

(ods destination),(proc report statement options), (define statement for var3), (result):

 

1. (html),(nowd missing nocompletecols),(define var3/displaySmiley Wink,(all class2 column suppressed)

2. (html),(nowd missing),                          (define var3/display nozeroSmiley Wink,(var3 column is suppressed, everything else is correct)

 

My mini-experiment suggests that for my particular report in which I used the html ods destination, the nozero define option produces the desired result while the no completecols proc report statement option does not.

 

 

 

 

 

 

SAS Super FREQ
Posts: 9,310

Re: proc report : trying to use across, group, and display definitions to generate complex report la

Hi:

  I get the same results when from this code -- using your generated (fake?) data -- I don't see any difference in the 3 tables.

cynthia

 

(code underneath screen shot)

same_output.png

 

 

data top3;
do report=1;
    do section = 1 to 2;
        if section = 1 then do;
            do class1 = 1 to 4;
                do class2 = 4 to 1 by -1;
                    do grp = 1 to 2;
                        if grp = 1 then do;
                            var1="z";var2="k";var4="z";var5="z";var6="z";
                            if class1 >1 & class2 <4 then var3="s"; else var3=" ";output;
                        end;
                        else do;
                        if grp = 2 then do;
                            var1="y";var2=" ";var4="y";var5="y";var6="y";
                            if class1 >1 & class2 <4 then var3="r";else var3=" ";output;
                        end;
                        end;
                        end;
                end;
            end;
        end;
        if section = 2 then do;
            do class1 = 5 to 7;
                do class2 = 4 to 1 by -1;
                        do grp = 1 to 2;
                        if grp = 1 then do;
                            var1="z";var2=" ";var3=" ";var4=" ";var5=" ";var6=" ";output;
                         end;
                        else do;
                        if grp = 2 then do;
                            var1="y";var2=" ";var3=" ";var4=" ";var5=" ";var6=" ";output;
                        end;
                        end;
                    end;
                end;
            end;
        end;
    end;
end;
run;

proc print data=top3;
run;

ods rtf file='c:\temp\version3.rtf';
proc report data=top3 nowd missing nocompletecols;
title "version 3 nocompletecols";
column report section class1 grp  
       class2,(var2 var1 var3) (var4 var5 var6)  dummyvar;
define report/group noprint;
define section/group noprint;
define class1/group order=data;
define grp/group noprint;
define class2/across order=data ;
define var2/display ;
define var1/display ;
define var3/display ;
define var4/group ;
define var5/group ;
define var6/group ;
define dummyvar/computed noprint;
*break before section/skip;
break after section/  ;
compute after section;
  line ' ';
endcomp;
compute dummyvar;
    dummyvar=1;
endcomp;
run;

proc report data=top3 nowd missing ;
title "version 3 nozero on CLASS2";
column report section class1 grp  
       class2,(var2 var1 var3) (var4 var5 var6)  dummyvar;
define report/group noprint;
define section/group noprint;
define class1/group order=data;
define grp/group noprint;
define class2/across order=data nozero;
define var2/display ;
define var1/display ;
define var3/display ;
define var4/group ;
define var5/group ;
define var6/group ;
define dummyvar/computed noprint;
*break before section/skip;
break after section/  ;
compute after section;
  line ' ';
endcomp;
compute dummyvar;
    dummyvar=1;
endcomp;
run;

proc report data=top3 nowd missing ;
title "version 3 nozero on VAR3";
column report section class1 grp  
       class2,(var2 var1 var3) (var4 var5 var6)  dummyvar;
define report/group noprint;
define section/group noprint;
define class1/group order=data;
define grp/group noprint;
define class2/across order=data;
define var2/display ;
define var1/display ;
define var3/display nozero;
define var4/group ;
define var5/group ;
define var6/group ;
define dummyvar/computed noprint;
*break before section/skip;
break after section/  ;
compute after section;
  line ' ';
endcomp;
compute dummyvar;
    dummyvar=1;
endcomp;
run;

ods rtf close;

 

 

Occasional Contributor
Posts: 15

Re: proc report : trying to use across, group, and display definitions to generate complex report la

Posted in reply to Cynthia_sas

That's interesting.  I'm using SAS 9.3 so maybe that's why.  Thanks for following up.  I really do appreciate all of your help.

SAS Super FREQ
Posts: 9,310

Re: proc report : trying to use across, group, and display definitions to generate complex report la

Well, you can prove that to yourself by running my code on your system. I am using 9.4 M3.
cynthia
Occasional Contributor
Posts: 15

Re: proc report : trying to use across, group, and display definitions to generate complex report la

Posted in reply to Cynthia_sas

Hi Cynthia,

 

I'm not sure if I should start a new thread or not as the question I am posing is highly related to my original post:

 

What follows is a data step that generates a 'new' input data set for my REPORT procedure.  My report is pretty much where I want it, except now my issue is there are 3 rows inserted after grp=1 and grp=2 when section=2. 

 

If you run the code, you see in section=1 of the report, that the "z" value of the var1 and var4 variables for grp=1 is paired with the "y" value of grp=2 as the "y" value follows immediately beneath the "z" value in the next row of the report.

 

In section=2, for class1=2, you see that the the "z" and "y" (grp=1,grp=2) values have 3 rows separating them.  My question is, how do I suppress the display of the 3 "empty" rows between the "z" and "y" values for variable var1 in section=2 of my report so that the "z" and "y" values of var1 and var4 'pair' up as they do in section=1 and everything else in my report remains unchanged.

 

You will note in my REPORT procedure code that I've tried several of the REPORT STATEMENT options: 'missing' 'nocompletecols' 'nocomplete' rows. None seem to fix my issue.  I even added 'nozero' options in the var1 and var4 define statements but they do not seem to fix my problem. Please note the suppression of the display of var3 is intended when class2=4.

 

Please advise.

 

Many thanks.

 

data have;
length report section class1 class2 grp 8 var2 var1 var4 var3 $8;
do report=1;
    do section = 1 to 2;
        if section = 1 then do;
            do class1=0 to 1;
                do class2 = 4 to 1 by -1;
                if class2 = 4 then class3 = " "; else class3=class2;
                    do grp = 1 to 2;

                        if class1 = 0 then do;
                            
                            if class2 = 4 then do;

                                if grp = 1 then do;

                                    var2="k"; var1="1"; var4=" "; var3=" ";output;

                                end;
                                else do;
                                if grp = 2 then do;

                                    var2=" "; var1=" "; var4=" "; var3=" ";output;

                                end;
                                end;

                            end;
                            else do;
                            if class2 = 3 then do;

                                if grp = 1 then do;

                                    var2="k"; var1="z"; var4="z"; var3=" ";output;

                                end;
                                else do;
                                if grp = 2 then do;

                                    var2=" "; var1="y"; var4="y"; var3=" ";output;

                                end;
                                end;

                            end;
                            else do;
                            if class2 = 2 then do;

                                if grp = 1 then do;

                                    var2="k"; var1="z"; var4="z"; var3=" ";output;

                                end;
                                else do;
                                if grp = 2 then do;

                                    var2=" "; var1="y"; var4="y"; var3=" ";output;

                                end;
                                end;

                            end;
                            else do;
                            if class2 = 1 then do;

                                if grp = 1 then do;

                                    var2="k"; var1="z"; var4="z"; var3=" ";output;

                                end;
                                else do;
                                if grp = 2 then do;

                                    var2=" "; var1="y"; var4="y"; var3=" ";output;

                                end;
                                end;

                            end;
                            end;
                            end;
                            end;

                        end;
                        else do;
                        if class1 = 1 then do;

                            if class2 = 4 then do;

                                if grp = 1 then do;

                                    var2="k"; var1="z"; var4=" "; var3=" ";output;

                                end;
                                else do;
                                if grp = 2 then do;

                                    var2=" "; var1="y"; var4=" "; var3=" ";output;

                                end;
                                end;

                            end;
                            else do;
                            if class2 = 3 then do;

                                if grp = 1 then do;

                                    var2="k"; var1="z"; var4="z"; var3="s";output;

                                end;
                                else do;
                                if grp = 2 then do;

                                    var2=" "; var1="y"; var4="y"; var3="r";output;

                                end;
                                end;

                            end;
                            else do;
                            if class2 = 2 then do;

                                if grp = 1 then do;

                                    var2="k"; var1="z"; var4="z"; var3="s";output;

                                end;
                                else do;
                                if grp = 2 then do;

                                    var2=" "; var1="y"; var4="y"; var3="r";output;
                                end;
                                end;

                            end;
                            else do;
                            if class2 = 1 then do;

                                if grp = 1 then do;

                                    var2="k"; var1="z"; var4="z"; var3="s";output;

                                end;
                                else do;
                                if grp = 2 then do;

                                    var2=" "; var1="y"; var4="y"; var3="r";output;

                                end;
                                end;

                            end;
                            end;
                            end;
                            end;


                        end;
                        end;
                        


                    end;
                end;
            end;
        end;
        if section = 2 then do;    
            class3 = " ";
            do class1=2;
                do class2 = 4 to 1 by -1;
                    do grp = 1 to 2;

                        if class1 = 2 then do;

                            if class2 = 4 then do;

                                if grp = 1 then do;

                                    var2=" "; var1="z"; var4=" "; var3=" ";output;

                                end;
                                else do;
                                if grp = 2 then do;

                                    var2=" "; var1="y"; var4=" "; var3=" ";output;

                                end;
                                end;

                            end;
                            else do;
                            if class2 = 3 then do;

                                if grp = 1 then do;

                                    var2=" "; var1="z"; var4=" "; var3=" ";output;

                                end;
                                else do;
                                if grp = 2 then do;

                                    var2=" "; var1="y"; var4=" "; var3=" ";output;

                                end;
                                end;

                            end;
                            else do;
                            if class2 = 2 then do;

                                if grp = 1 then do;

                                    var2=" "; var1="z"; var4=" "; var3=" ";output;

                                end;
                                else do;
                                if grp = 2 then do;

                                    var2=" "; var1="y"; var4=" "; var3=" ";output;

                                end;
                                end;

                            end;
                            else do;
                            if class2 = 1 then do;

                                if grp = 1 then do;

                                    var2=" "; var1="z"; var4=" "; var3=" ";output;

                                end;
                                else do;
                                if grp = 2 then do;

                                    var2=" "; var1="y"; var4=" "; var3=" ";output;

                                end;
                                end;

                            end;
                            end;
                            end;
                            end;


                        end;




                    end;
                end;
            end;
        end;
    end;
end;
run;

proc print data=have;
run;
proc report data=have nowd missing nocompleterows nocompletecols;
title "version 3: plain no formatting";
column report section class1 grp  class2,(var2 var1 var3) class3,(var4)  dummyvar;
define report/group noprint;
define section/group noprint;
define class1/group order=data width=6 nozero;
define grp/group noprint;
define class2/across order=data;
define var2/display width=8;
define var1/display width=8 nozero;
define var3/display width=8 nozero;
define class3/across order=data;
define var4/display width=8 nozero;
define dummyvar/computed noprint;
compute dummyvar;
    dummyvar=1;
endcomp;
run;

SAS Super FREQ
Posts: 9,310

Re: proc report : trying to use across, group, and display definitions to generate complex report la

Hi:

  I'm stumped. All I can think of is that your convoluted data structure and combination of missing values for some of the order/group and across variables is causing PROC REPORT to put a row down where it should collapse.

 

  Using your data, I can duplicate the blank rows for section=2, as shown below:

too_many_blanks.png

 

However, if I remove CLASS3 and VAR4 from the report, everything collapses correctly. So somehow, all your complicated manipulations to make data cause PROC REPORT to break when you put CLASS3 and VAR4 back on the report.

 

Do you have REAL data that is in this strange structure with this combination of missing? If so, then I recommend opening a track with Tech Support.

 

cynthia

 

Here's the code I used (with your data) that produced the above screen shot. Note that I removed ALL the NOPRINTS to reveal all the columns for debugging:

proc report data=have nowd missing nocompleterows nocompletecols;
title "1) Original Code -- has unwanted blank lines for section=2";
column report section class1 grp  class2,(var2 var1 var3) class3,(var4)  dummyvar;
define report/group ;
define section/group ;
define class1/group order=data width=6 ;
define grp/group ;
define class2/across order=data;
define var2/display width=8;
define var1/display width=8 nozero;
define var3/display width=8 nozero;
define class3/across order=data;
define var4/display width=8 nozero;
define dummyvar/computed ;
compute dummyvar;
    dummyvar=1;
endcomp;
run;
   
proc report data=have nowd missing nocompleterows nocompletecols;
title "2) Original Code -- without class3 and var4 combo";
title2 'Everything collapses as desired no unwanted lines for section=2';
column report section class1 grp  class2,(var2 var1 var3) dummyvar;
define report/group ;
define section/group ;
define class1/group order=data width=6 ;
define grp/group ;
define class2/across order=data;
define var2/display width=8;
define var1/display width=8 nozero;
define var3/display width=8 nozero;
define dummyvar/computed ;
compute dummyvar;
    dummyvar=1;
endcomp;
run;
Occasional Contributor
Posts: 15

Re: proc report : trying to use across, group, and display definitions to generate complex report la

Posted in reply to Cynthia_sas

Hi Cynthia,

 

Thanks for trying. 

 

I too suspected that the combinations of missing were likely the issue. 

 

I'll keep working on it and if I come up with a solution, I'll post.

 

Cheers

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 386 views
  • 0 likes
  • 2 in conversation