Proc Report Problem

[Jayla]

Hi! .o/

I'm implementing some changes into an existing report that I currently produce.

The report is produced weekly and reports data down to customer level. If a customer was not on the report last week it should appear in the colour red, if it was removed from the report 14 days ago (or less) and has reappeared again it will appear in orange. All other entries should appear in a black test.

I was fine at this point and with the report being in HTML I simpily built up the HTML output in the datasteps before calling using the ODS statements.

However the guy in the business who is using the report would like the data to have different coloured cell backgrounds and not change the colour of the text.

I've tried using compute after to change the background colour of the cells with each change in the account number, but this doesn't work for me. The code is:

ods html file="t:\james\reports\sas\format_test.html";
proc report data=rwork.plus_3_trucks_detail nowd;
			columns total_faults total_trucks account_no account_name
					address html_detail;
			define total_faults / group descending 'Total Faults';
			define total_trucks / group descending 'Number of Truck Rolls';
			define account_no / display 'Account Number';
			define account_name / display 'Account Name';
			define address / display 'Address';
			define html_detail / display 'Link to Details'; 

			compute after account_no;
				if out_type='New' then
					call define('_col_','style','style=(background=red foreground=white)');
				if out_type='Back' then
					call define('_col_','style','style=(background=grey foreground=white)');
				if out_type='Norm' then
					call define('_col_','style','style=(background=white foreground=black)');
			endcomp;
run;
ods html close;

When I run this I get the following warning:

ERROR: You can only BREAK on GROUPing and ORDERing variables.
NOTE: Groups are not created because the usage of account_no is DISPLAY.
NOTE: There were 204 observations read from the data set RWORK.PLUS_3_TRUCKS_DETAIL.
NOTE: PROCEDURE REPORT used:
      real time           0.57 seconds
      cpu time            0.07 seconds

Now the report uses the fault totals as the grouping variables. This is for the output purposes to make the report easier to read. So I can't really change the order by and group by variables.

I suppose my straight forward question is can compute after be used with a keyword that basically says process the code block after each row produced? If not any suggestions?

Many thanks.

 

[Jayla]

OK my apologies, I think I have solved one part of my problem, however I still have an issue:

ods html file="t:\james\reports\sas\format_test.html" style=style.newfont;
proc report data=rwork.plus_3_trucks_detail nowd;
			columns total_faults total_trucks account_no account_name
					address html_detail out_type;
			define total_faults / group descending 'Total Faults';
			define total_trucks / group descending 'Number of Truck Rolls';
			define account_no / display 'Account Number';
			define account_name / display 'Account Name';
			define address / display 'Address';
			define html_detail / display 'Link to Details'; 

			compute before;
				if out_type='New' then
					call define('_c1_','style','style=[background=red foreground=white]');
				if out_type='Back' then
					call define('_c1_','style','style=[background=blue foreground=white]');
				if out_type='Norm' then
					call define('_c1_','style','style=[background=white foreground=black]');
			endcomp;
run;
ods html close;

Log states:

NOTE: There were 204 observations read from the data set RWORK.PLUS_3_TRUCKS_DETAIL.
NOTE: PROCEDURE REPORT used:
      real time           0.54 seconds
      cpu time            0.10 seconds


NOTE: Writing HTML Body file: t:\james\reports\sas\format_test.html

NOTE: Groups are not created because the usage of account_no is DISPLAY.
NOTE: There were 204 observations read from the data set RWORK.PLUS_3_TRUCKS_DETAIL.
NOTE: PROCEDURE REPORT used:
      real time           0.54 seconds
      cpu time            0.07 seconds

So it appears nothing in my compute block is being executed. Is this because groups aren't being created as in the warning? Or am I using the define statement incorrectly?

Thanks.

 

[Jayla]

Sorry for triple post I can't see an edit post option. Maybe I should prove read my posts with more care.

The second post was refering to my remaining problem being none of the rows have any colour changes applied to them. I assume this is because of either incorrect define statement usage of because as the warning states no groups are being created.

I am a little suprised at the warning as when I view the report I do have groups there. eg:

Faults Trucks Customer
10 6 XXXXXXXXXX
YYYYYYYYYY
9 6 ZZZZZZZZZZ
9 3 AAAAAAAAAA
BBBBBBBBBB
5 2 CCCCCCCCCC

I appreciate any advice or suggestions.

 

[ChrisW75]

Aha! I've been bitten by this one a couple of time.

Change your column statement to move out_type to BEFORE the column you want to change colour.

The problem here is that SAS works in Proc Report column by column, so it has no knowledge of anything in any columns it has not yet displayed. I'm not sure if you can use this to set up the entire rows background colour, but you should be able to do it for a single column, so long as it IS the column the colour depends on, or it comes AFTER the column that the colour depends on.

When I got hit by this, I had two columns that I was interested in, column A and column B, if column A was not equal to column B, I wanted them highlighted in Red. I could only get it to highlight correctly on the second Column though. When it tested Column A (defined first) as far as it was concerned, column B was blank as it had not yet processed that column.

Very tricksy.
I hope this helps.

 

[Jayla]

Thank you for your help Chris. It certainly provoked me to explore other variations of the syntax.

I ended up with:

ods html file="t:\james\reports\sas\format_test.html" style=style.newfont;
proc report data=rwork.plus_3_trucks_detail nowd;
			columns total_faults total_trucks account_no account_name
					address out_type html_detail;
			define total_faults / group descending 'Total Faults';
			define total_trucks / group descending 'Number of Truck Rolls';
			define account_no / display 'Account Number';
			define account_name / display 'Account Name';
			define address / display 'Address';
			define out_type / order ;
			define html_detail / display 'Link to Details'; 
			compute out_type;
				if out_type='New' then
					call define(_row_,'style','style=[background=red foreground=white]');
				if out_type='Back' then
					call define(_row_,'style','style=[background=blue foreground=white]');
				if out_type='Norm' then
					call define(_row_,'style','style=[background=green foreground=black]');
			endcomp;
run;
ods html close;

And it now produces colours along the whole row. Which is ideal. Just need to figure out how to get the order to stop suppressing entries (ie. making groups). Seems order is required to make the groups which make the compute statement execute, unfortunately it also causes the out_type variable to be an empty string in the compute statements. :S

 

[Jayla]

Just a FYI

I simpily included display in the define statement for the variable out_type and then included noprint to suppress the actual display of it.

I never realised they could all be used together. Thanks for the help provided.