The SAS Output Delivery System and reporting techniques

Advanced proc report "Number of Total" and PCT

Accepted Solution Solved
Reply
Contributor alr
Contributor
Posts: 39
Accepted Solution

Advanced proc report "Number of Total" and PCT

Hi,

I have following table:

table.png

Is it possible to present it like this using proc report?

"1 of 5" comes from variable kpin_0 and kpit_0 and so on for the other.

report.png


Accepted Solutions
Solution
‎04-15-2014 07:28 AM
Super User
Posts: 9,874

Re: Advanced proc report "Number of Total" and PCT

Sure. You need read proc report documentation more!

ods listing close;

ods escapechar ='~';

ods html file='x.html' style=sasweb ;

proc report data=have nowd;

columns kpiid kpin_0 kpin_1 kpin_2 kpit_0 kpit_1 kpit_2  kpi_0 _kpi_0 kpi_1 _kpi_1 kpi_2 _kpi_2 ;

define kpiid/display;

define kpi_0/display noprint;

define kpi_1/display noprint;

define kpi_2/display noprint;

define kpin_0/display noprint;

define kpin_1/display noprint;

define kpin_2/display noprint;

define kpit_0/display noprint;

define kpit_1/display noprint;

define kpit_2/display noprint;

define _kpi_0/computed 'kpi_0';

define _kpi_1/computed 'kpi_1';

define _kpi_2/computed 'kpi_2';

compute _kpi_0/character length=20;

if kpiid=1 then _kpi_0=catx(' ',kpin_0,'of',kpit_0,'~n',put(kpin_0/kpit_0,percent8.));

else do;

  _kpi_0=put(kpi_0,best6.);

  if 0<=kpi_0<=15 then call define(_col_,'style','style={background=red');

   else if 16<=kpi_0<=80 then  call define(_col_,'style','style={background=orange');

        else if 80<=kpi_0 then  call define(_col_,'style','style={background=yellow');

end;

endcomp;

compute _kpi_1/character length=20;

if kpiid=1 then _kpi_1=catx(' ',kpin_1,'of',kpit_1,'~n',put(kpin_1/kpit_1,percent8.));

else do;

_kpi_1=put(kpi_1,best6.);

if 0<=kpi_1<=15 then call define(_col_,'style','style={background=red');

   else if 16<=kpi_1<=80 then  call define(_col_,'style','style={background=orange');

        else if 80<=kpi_1 then  call define(_col_,'style','style={background=yellow');

end;

endcomp;

compute _kpi_2/character length=20;

if kpiid=1 then _kpi_2=catx(' ',kpin_2,'of',kpit_2,'~n',put(kpin_2/kpit_2,percent8.));

else do;

  _kpi_2=put(kpi_2,best6.);

  if 0<=kpi_2<=15 then call define(_col_,'style','style={background=red');

   else if 16<=kpi_2<=80 then  call define(_col_,'style','style={background=orange');

        else if 80<=kpi_2 then  call define(_col_,'style','style={background=yellow');

end;

endcomp;

run;

ods html close;

ods listing;

Xia Keshan

Message was edited by: xia keshan

View solution in original post


All Replies
Super User
Super User
Posts: 7,720

Re: Advanced proc report "Number of Total" and PCT

Could you create a text string for each set, e.g:

data want;

     attrib kpi_0c kpi_1c kpi_2c format=$20.;

     if kpiid=1 then do;

          kpi_0c=strip(put(kpin_0,best.))||" of "||strip(put(kpit_0,best.))||"^{newline}"||strip(put(kpi_0 * 100,best.))||"%" ;

          kpi_1c=...

     end;

     if kpiid=2 then ...

...

run;

Note the ^{newline} in the first row should be a newline, which depends on your output file type.  This is an escape character so you need ods escapechar="^"; and I am assuming RTF output.  If other then you need to change per your doc type.  Then just proc report; column kpiid kpi_0c kpi_1c...

Contributor alr
Contributor
Posts: 39

Re: Advanced proc report "Number of Total" and PCT

Thank you.

I hoped to be able to use compute.

Because I still need to format the cells to get different background colours depending on the values.

Super User
Super User
Posts: 7,720

Re: Advanced proc report "Number of Total" and PCT

Well, you can still use the numbers in a compute block to change font/color.  Just add them to your column statement and in the define line set them as noprint, that way they are available to compute, but do not come out in the output.  You could put the logic to append all the data in your compute section, however there are reasons I wouldn't do that, especially if you are trying to validate the output as its easier to see the concatenated variable in the dataset (although you could output the proc report dataset itself).

Contributor alr
Contributor
Posts: 39

Re: Advanced proc report "Number of Total" and PCT

Yes, but now my variables are character and I can’t make format for change the background colors based on the values.

My code is like this but for example for kpiid=2 I need to present “Number of Total and PCT”.

proc format;

VALUE myfmt

0 - 15 = red

16 - 80 = orange

80 - high = yellow

;

run;

proc report data=mydata;

   col kpiid kpi_4 kpi_3 kpi_2 kpi_1 kpi_0;

   define kpiid       / display  ;

   define kpi_0-kpi_4 / display  ;

   compute kpi_0;

      if kpiid = 1 then do;

         call define ('kpi_0','style','style={background=myfmt.}');

         call define ('kpi_1','style','style={background=myfmt.}');

         call define ('kpi_2','style','style={background=myfmt.}');

         call define ('kpi_3','style','style={background=myfmt.}');

         call define ('kpi_4','style','style={background=myfmt.}');

      end;

   endcomp;

run;

Super User
Posts: 9,874

Re: Advanced proc report "Number of Total" and PCT

Sure. check compute block:

data have;
infile cards truncover;
input kpiid kpi_0 kpi_1 kpi_2 kpin_0 kpin_1 kpin_2 kpit_0 kpit_1 kpit_2;
cards;
1 0.2 0.2 1 1 2 5 5 10 5
2 57 60 21
3 9 10 11
;
run;

ods listing close;
ods escapechar ='~';
ods html file='x.html' style=sasweb ;
proc report data=have nowd;
columns kpiid kpin_0 kpin_1 kpin_2 kpit_0 kpit_1 kpit_2  kpi_0 _kpi_0 kpi_1 _kpi_1 kpi_2 _kpi_2 ;
define kpiid/display;
define kpi_0/display noprint;
define kpi_1/display noprint;
define kpi_2/display noprint;
define kpin_0/display noprint;
define kpin_1/display noprint;
define kpin_2/display noprint;
define kpit_0/display noprint;
define kpit_1/display noprint;
define kpit_2/display noprint;
define _kpi_0/computed 'kpi_0';
define _kpi_1/computed 'kpi_1';
define _kpi_2/computed 'kpi_2';

compute _kpi_0/character length=20;
if kpiid=1 then _kpi_0=catx(' ',kpin_0,'of',kpit_0,'~n',put(kpin_0/kpit_0,percent8.));
 else _kpi_0=put(kpi_0,best6.);
endcomp;
compute _kpi_1/character length=20;
if kpiid=1 then _kpi_1=catx(' ',kpin_1,'of',kpit_1,'~n',put(kpin_1/kpit_1,percent8.));
 else _kpi_1=put(kpi_1,best6.);
endcomp;
compute _kpi_2/character length=20;
if kpiid=1 then _kpi_2=catx(' ',kpin_2,'of',kpit_2,'~n',put(kpin_2/kpit_2,percent8.));
 else _kpi_2=put(kpi_2,best6.);
endcomp;
run;
ods html close;
ods listing;

Xia Keshan

Contributor alr
Contributor
Posts: 39

Re: Advanced proc report "Number of Total" and PCT

Thank you.

Is it possible to change the background colors based on the values as well using a format?

Solution
‎04-15-2014 07:28 AM
Super User
Posts: 9,874

Re: Advanced proc report "Number of Total" and PCT

Sure. You need read proc report documentation more!

ods listing close;

ods escapechar ='~';

ods html file='x.html' style=sasweb ;

proc report data=have nowd;

columns kpiid kpin_0 kpin_1 kpin_2 kpit_0 kpit_1 kpit_2  kpi_0 _kpi_0 kpi_1 _kpi_1 kpi_2 _kpi_2 ;

define kpiid/display;

define kpi_0/display noprint;

define kpi_1/display noprint;

define kpi_2/display noprint;

define kpin_0/display noprint;

define kpin_1/display noprint;

define kpin_2/display noprint;

define kpit_0/display noprint;

define kpit_1/display noprint;

define kpit_2/display noprint;

define _kpi_0/computed 'kpi_0';

define _kpi_1/computed 'kpi_1';

define _kpi_2/computed 'kpi_2';

compute _kpi_0/character length=20;

if kpiid=1 then _kpi_0=catx(' ',kpin_0,'of',kpit_0,'~n',put(kpin_0/kpit_0,percent8.));

else do;

  _kpi_0=put(kpi_0,best6.);

  if 0<=kpi_0<=15 then call define(_col_,'style','style={background=red');

   else if 16<=kpi_0<=80 then  call define(_col_,'style','style={background=orange');

        else if 80<=kpi_0 then  call define(_col_,'style','style={background=yellow');

end;

endcomp;

compute _kpi_1/character length=20;

if kpiid=1 then _kpi_1=catx(' ',kpin_1,'of',kpit_1,'~n',put(kpin_1/kpit_1,percent8.));

else do;

_kpi_1=put(kpi_1,best6.);

if 0<=kpi_1<=15 then call define(_col_,'style','style={background=red');

   else if 16<=kpi_1<=80 then  call define(_col_,'style','style={background=orange');

        else if 80<=kpi_1 then  call define(_col_,'style','style={background=yellow');

end;

endcomp;

compute _kpi_2/character length=20;

if kpiid=1 then _kpi_2=catx(' ',kpin_2,'of',kpit_2,'~n',put(kpin_2/kpit_2,percent8.));

else do;

  _kpi_2=put(kpi_2,best6.);

  if 0<=kpi_2<=15 then call define(_col_,'style','style={background=red');

   else if 16<=kpi_2<=80 then  call define(_col_,'style','style={background=orange');

        else if 80<=kpi_2 then  call define(_col_,'style','style={background=yellow');

end;

endcomp;

run;

ods html close;

ods listing;

Xia Keshan

Message was edited by: xia keshan

Contributor alr
Contributor
Posts: 39

Re: Advanced proc report "Number of Total" and PCT

Thank you

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 625 views
  • 2 likes
  • 3 in conversation