BookmarkSubscribeRSS Feed
emera86
Quartz | Level 8

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!

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

emera86
Quartz | Level 8

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!

 

 

PaigeMiller
Diamond | Level 26

@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
BrunoMueller
SAS Super FREQ

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;
Cynthia_sas
SAS Super FREQ
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
emera86
Quartz | Level 8

Thank you all for your help!!

 

@BrunoMueller, I tried your approach but I was still getting the wrong result that I was depicting in my original post... :S

 

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!

Cynthia_sas
SAS Super FREQ
Hi:
My paper has an example of using ACROSS items with character variables, just FYI. Preprocessing is also an option.
cynthia
emera86
Quartz | Level 8

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!! 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1996 views
  • 1 like
  • 5 in conversation