topic Re: Compute block in Proc Report in SAS Programming

Compute block in Proc Report

SatishR — Wed, 24 Jan 2024 12:04:02 GMT

Dear Team,

I am trying to use compute block in proc report to highlight the response time is greater than 10m mins but still not getting expected output. below is my code


proc report data=s1;
column City_name  Priority,responsetime;
define city_name/group "DCR Name";
define priority/across "Priority";
define responsetime/mean format=time8. "Response Time";
 compute responsetime;
    if  mean(responsetime)>'00:10:00't then do;
      call define('responsetime.mean', 'style', 'style=[background=red foreground=white font_weight=bold]');
    end;
  endcomp;
run;

My responsetime is in time8. format only and even I tried by passing numeric value as like

if mean(responsetime)>600 then do;

Kindly suggest me for necessary changes

Re: Compute block in Proc Report

Patrick — Wed, 24 Jan 2024 12:20:08 GMT

@SatishR Not that many people providing answers regularly in this forum use Proc Report that often to just spot "any" issues and to provide untested code changes that then actually also work.

You will get more people responding with tested code if you provide sample source data and also show us the desired result.

Please provide sample source data ideally via SAS data step code that creates the sample data that you then use with the Proc Report code - and then show us the desired result or if not feasible describe it as detailed as possible.

Basically.... If you would be asked to help, what would you like/need to get as information?

Re: Compute block in Proc Report

PaigeMiller — Wed, 24 Jan 2024 12:24:06 GMT

Instructions and examples of how to provide sample data as SAS data step code.

Re: Compute block in Proc Report

Kurt_Bremser — Wed, 24 Jan 2024 12:46:23 GMT

You cannot use an unaliased variable in the COMPUTE, you need to also specify the statistic:

if responsetime.mean > '00:10:00't then do;

Re: Compute block in Proc Report

FreelanceReinh — Wed, 24 Jan 2024 13:39:50 GMT

Hello @SatishR,

(After a quick look into Carpenter's Complete Guide to the SAS REPORT Procedure ...)

I think using column number references (_c1_, _c2_, ...) will work better:

 compute responsetime;
    if _c2_>'0:10't then call define('_c2_', 'style', 'style=[background=red foreground=white font_weight=bold]');
    if _c3_>'0:10't then call define('_c3_', 'style', 'style=[background=red foreground=white font_weight=bold]');
    /* ... possibly more of these IF-THEN statements, depending on the number of PRIORITY values ... */
  endcomp;

If there are many distinct PRIORITY values, you may want to switch to the format approach to traffic highlighting:

proc format;
value backgrfmt '0:10't<-high = 'red';
value foregrfmt '0:10't<-high = 'white';
value fontwtfmt '0:10't<-high = 'bold';
run;

Then you don't even need a COMPUTE block; just add a STYLE option to the appropriate DEFINE statement and refer to the formats:

define responsetime/mean format=time8. "Response Time"
       style(column)=[background=backgrfmt. foreground=foregrfmt. font_weight=fontwtfmt.];

Re: Compute block in Proc Report

Cynthia_sas — Wed, 24 Jan 2024 15:16:19 GMT

Hi:

@FreelanceReinh is correct. You cannot use the simple varname.stat in a COMPUTE block when the variable is nested under an ACROSS variable. You MUST use the absolute column numbers. There are a lot of previous postings in the forum about using absolute column numbers in a COMPUTE block.

Cynthia

Re: Compute block in Proc Report

ballardw — Wed, 24 Jan 2024 19:12:58 GMT

Best is to provide actual data as working data step.

I have made some fake data using your variables and an approach to do this with Proc TABULATE and a format to attach to the statistic to set color.

data s1;
  input city_name $ priority $ responsetime :time.;
  format responsetime time.;
datalines;
AAA  first  00:01:00
AAA  first  00:03:00
AAA  first  00:05:00
BBB  first  00:11:00
BBB  first  00:13:00
BBB  first  00:15:00
AAA  last  00:07:00
AAA  last  00:12:00
AAA  last  00:15:00
BBB  last  00:02:00
BBB  last  00:06:00
BBB  last  00:09:00
;

proc format ;
value mycolor
'00:10:00't - high ='red'
;
run;

proc tabulate data=s1;
  class city_name priority;
  var responsetime;
  table city_name,
        priority*responsetime*mean*[style= [backgroundcolor=mycolor.]]*f=time.
  ;
run;

Tabulate behaves a bit differently than Proc report (no compute block at all for one). You can specify style appearance in conjunction with a statistic (or other variable) as shown. The Format evaluates the value that is to be displayed and then uses the resulting value, i.e. anything 10 minutes or greater in the desired color.

Re: Compute block in Proc Report

SatishR — Thu, 25 Jan 2024 09:50:46 GMT

Hi I have below sample data and I have to highlight avg of resposetime is greater than 10 Minutes in proc report.

data s1;
input Sr 1-2 City_name $ 3-12 Event_id $ 14-22 Responsetime $ 24-32 Priority $ 33-35;
cards;
1 Ahmednagar CFS459872 00:14:00 P1
2 Ahmednagar CFS462218 00:52:25 P1
3 Ahmednagar CFS458172 22:48:25 P1
4 Ahmednagar CFS458822 00:30:09 P1
5 Ahmednagar CFS461207 00:32:46 P2
;

Expected output shall be like this,

Hope this input will understand my query.

Re: Compute block in Proc Report

FreelanceReinh — Thu, 25 Jan 2024 11:14:35 GMT

@SatishR wrote:

Expected output shall be like this,

Thanks for providing sample data and the desired output.

Let's first improve the type of variable Responsetime. Your own PROC REPORT code used it as a numeric variable (as it should), but it appears to be a character variable in your sample data.

data have;
set s1(rename=(responsetime=rt));
Responsetime=input(rt,time8.);
format Responsetime e8601tm.;
drop rt;
run;

Since '06:06:15't is obviously greater than '00:10:00't, but you didn't highlight it, I assume that you only want to highlight times with priority P2. (If you change your mind and would like to highlight priority-P1 times as well, just add the analogous IF-THEN statement for _c2_ below; cf. my first reply.)

Using this code

ods html file='C:\Temp\want.html' style=pearl;

proc report data=have;
column City_name Responsetime, Priority;
define City_name / group;
define priority / across ' ';
define responsetime / mean format=e8601tm.;
compute responsetime;
  if _c3_>'0:10't then call define('_c3_', 'style', 'style=[background=yellow]');
endcomp;
run;

ods html close;

I obtain this table:

Re: Compute block in Proc Report

ballardw — Thu, 25 Jan 2024 15:21:47 GMT

If you want to work with date, time or datetime values, such as calculating intervals, incrementing or things like mean time, range if times or standard deviation then you must use SAS date, time or datetime values which are numeric.

Your example shows use of "time" as character values. Consider what would the mean of "ABC" , "PDQ" and "XYZ" be? There really isn't a lot of agreement what a "mean" or other calculation for character values would be.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354 has a PDF with much information about dates and times.

@SatishR wrote:

Hi I have below sample data and I have to highlight avg of resposetime is greater than 10 Minutes in proc report.

data s1;
input Sr 1-2 City_name $ 3-12 Event_id $ 14-22 Responsetime $ 24-32 Priority $ 33-35;
cards;
1 Ahmednagar CFS459872 00:14:00 P1
2 Ahmednagar CFS462218 00:52:25 P1
3 Ahmednagar CFS458172 22:48:25 P1
4 Ahmednagar CFS458822 00:30:09 P1
5 Ahmednagar CFS461207 00:32:46 P2
;

Expected output shall be like this,

Hope this input will understand my query.

Re: Compute block in Proc Report

SatishR — Fri, 26 Jan 2024 06:13:41 GMT

Thank you, @FreelanceReinh , for providing a solution. I have received the answer to my query.

Re: Compute block in Proc Report

SatishR — Fri, 26 Jan 2024 06:20:56 GMT

@FreelanceReinh Thank you..This solution is also acceptable.