Hi,
I have following table:
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.
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
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...
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.
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).
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;
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
Thank you.
Is it possible to change the background colors based on the values as well using a format?
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
Thank you
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.