BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alr
Quartz | Level 8 alr
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

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

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...

alr
Quartz | Level 8 alr
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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).

alr
Quartz | Level 8 alr
Quartz | Level 8

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;

Ksharp
Super User

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

alr
Quartz | Level 8 alr
Quartz | Level 8

Thank you.

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

Ksharp
Super User

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

alr
Quartz | Level 8 alr
Quartz | Level 8

Thank you

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
  • 1657 views
  • 2 likes
  • 3 in conversation