The SAS Output Delivery System and reporting techniques

ACROSS in PROC REPORT is not grouping properly

Reply
Contributor
Posts: 46

ACROSS in PROC REPORT is not grouping properly

Hi there,


I'm having trouble with a PROC REPORT when using both an ACROSS variable and a GROUP variable. It seems that the GROUP is not working properly with the ACROSS: the results for each of the ACROSS groups appear in different lines instead of being in the same one. This screenshot may help to understand what is happening:

 

sample.jpg

 

In the first rows I have painted some red arrows to explain where the values should be.

 

Here you have the PROC REPORT code that I'm using;

 

proc report data=fich3 nowd headline style(header)={background=very light grey fontsize=8pt} missing 
                                             style(column)={fontsize=8pt } split='*';
  title "TEAES by patient- randomized patients";
  column orden  texto ('TEAES by patient and arm' alg, (x0 x1 x2 x3 x4 x5 x6) ) flag flag2;
  define texto / group 'System Organ Class/*Preferred term' f=$chart60. style(column)={cellwidth=6.0cm} flow;
  define alg / across nozero 'Arm' f=trt.;
  define orden / noprint order;
  define x0 / display f=$char12. 'Total*patients*n(%)' style(column)={cellwidth=2.0cm} flow;
  define x1 / display f=$char12. "Missing*n(%)"  style(column)={cellwidth=2.0cm} flow;
  define x2 / display f=$char12. "Mild*n(%)"  style(column)={cellwidth=2.0cm} flow;
  define x3 / display f=$char12. "Moderate*n(%)"  style(column)={cellwidth=2.0cm} flow;
  define x4 / display f=$char12. "Severe*n(%)"  style(column)={cellwidth=2.0cm} flow;
  define x5 / display f=$char12. "Life*Threatening*n(%)"  style(column)={cellwidth=2.0cm} flow;
  define x6 / display f=$char12. "Death*n(%)"  style(column)={cellwidth=2.0cm} flow;

define flag2   /computed noprint;
      define flag / display noprint;
      compute flag2;
           if flag=1 then flag2=1;
           else if flag ne 1  then flag2=0;
           if flag2=1 then do;
                 call define(_row_, "STYLE","STYLE=[BACKGROUND=very light grey  FONT_WEIGHT=BOLD]");
           end;
           if flag2=0 then do;
                 call define(_row_, "STYLE","STYLE=[BACKGROUND=white ] " );
           end;
   endcomp;
run;

If I filter the data set to use just a single value of the ACROSS variable it works as expected. It is just a duplicity of lines when you have more than one value.

 

Thank you in advance for any tip on how to solve this problem!

Super User
Super User
Posts: 9,193

Re: ACROSS in PROC REPORT is not grouping properly

As with all these "I am doing data processing in proc report" questions - do your processing before in a datastep, then proc report out the dataset.  Attempting to do data processing in a proc report can lead to hard to debug/maintain code.  For instance, your output can be achieve simply by left joining some sums onto the fich3 dataset, thus avoiding the need for all the proc report coding.

Contributor
Posts: 46

Re: ACROSS in PROC REPORT is not grouping properly

Thank you @RW9 for your quick response.

 

I was also considering the dataset preprocessing but I would like to implement this PROC REPORT in a macro so I need the code to be as general as possible and the ACROSS (if I make it work) would be easier to generalize in my macro.

 

I will give it a try though if there's no solution for my PROC REPORT problem.

 

Thank you for your sugesstion!

 

 

Respected Advisor
Posts: 2,647

Re: ACROSS in PROC REPORT is not grouping properly


emera86 wrote:

Thank you @RW9 for your quick response.

 

I was also considering the dataset preprocessing but I would like to implement this PROC REPORT in a macro so I need the code to be as general as possible and the ACROSS (if I make it work) would be easier to generalize in my macro.

 

I will give it a try though if there's no solution for my PROC REPORT problem.

 

Thank you for your sugesstion!

 

 


I agree with the advice to perform as much of the data manipulations in PROC SUMMARY and a data step before PROC REPORT. 

 

I disagree completely with your statement that doing this in a data step before PROC REPORT somehow violates the idea that you want your code to be as general as possible. ACROSS is a very nice feature but depending on how you do it, you may find it easier to implement in PROC SUMMARY and/or a data step. Don't lock yourself into doing everything inside of PROC REPORT.

--
Paige Miller
SAS Super FREQ
Posts: 806

Re: ACROSS in PROC REPORT is not grouping properly

you should be able to get what you want, see example below.

 

Some remarks:

  • All the "grouping/ordering" columns have the GROUP usage, order implies that you see all the detail records
  • We do need the _DUMMY variable, otherwise we get an error

Have a go.

 

 

data have;
  infile cards dlm="," dsd;
  input
    seqnr : 8.
    texto : $32.
    alg : 8.
    x0 : $16.
    x1 : $16.
  ;
  cards;
2,line1,1,1(1),1(1)
2,line1,2,1(2),1(2)
1,line2,1,10(abc),10(20)
1,line2,2,10(hello),10
3,,1,,
4,line4,1,4(1),40(1)
4,line4,2,4(2),40(2)

;

proc report data=have missing;
  column
    seqnr
    texto alg, (x0 x1)
    _dummy
  ;
  ;
  define seqnr / group;
  define texto / group;
  define alg / across;
  define x0 / display;
  define x1 / display;
  define _dummy / computed noprint;
run;
SAS Super FREQ
Posts: 9,253

Re: ACROSS in PROC REPORT is not grouping properly

Posted in reply to Bruno_SAS
Hi:
And, in addition to Bruno's excellent example, you might find this user-group paper specifically written about using ACROSS items to be useful: https://www.sas.com/content/dam/SAS/support/en/technical-papers/SAS388-2014.pdf

cynthia
Contributor
Posts: 46

Re: ACROSS in PROC REPORT is not grouping properly

Posted in reply to Bruno_SAS

Thank you all for your help!!

 

@Bruno_SAS, I tried your approach but I was still getting the wrong result that I was depicting in my original post... Smiley Frustrated

 

After reading more documentation (including @Cynthia_sas's reference) I've come to the conclusion that the origin of my problem is that the variables over which I'm doing the across are not numeric but char. ACROSS is specially designed to work with numeric variables (very useful to provide statistics and perform different analysis) but it does not interpret the char variables very well.

 

I have finally decided to walk around this problem by preprocessing the data set as @RW9 and @PaigeMiller were suggesting and I have already achieved my desired result.

 

Thank you again for your feedback!

SAS Super FREQ
Posts: 9,253

Re: ACROSS in PROC REPORT is not grouping properly

Hi:
My paper has an example of using ACROSS items with character variables, just FYI. Preprocessing is also an option.
cynthia
Contributor
Posts: 46

Re: ACROSS in PROC REPORT is not grouping properly

[ Edited ]
Posted in reply to Cynthia_sas

Hi @Cynthia_sas

 

I saw it, and I tried to apply it to my case, but it still didn't work, so I decided to give the preprocessing a try and it worked straightaway so I stop trying with the ACROSS. I'm sure there is a way to make it work, I'll try again next time I come across a similar problem to get to understand the two ways on which this problem can be approached.

 

Thank you anyway!! Smiley Happy

Ask a Question
Discussion stats
  • 8 replies
  • 213 views
  • 0 likes
  • 5 in conversation