topic Re: SAS ENTERPRISE GUIDE: TRAFFIC LIGHTING - REPORT WITH COLORS ON SUB-SET OF DATA in SAS Enterprise Guide

SAS ENTERPRISE GUIDE: TRAFFIC LIGHTING - REPORT WITH COLORS ON SUB-SET OF DATA

PPimentel_Chile — Thu, 03 Aug 2017 21:22:34 GMT

HI,

I´M TRYING TO APPLY COLORS

DETERMINING THE SMALLEST() 1 AND 2

FOR EACH SUB-SET OF DATA (7 CELLS) BY COLUMN (FONDO) AND ROW (DATE AND COMPANIES)

THE PROBLEM IS TO APPLY COLORS NOT ONLY BY COLUMN.

HOW WILL I APPLY THE COLORS IN ENTERPRISE GUIDE?

AN EXAMPLE OF THE REPORT IN EXCEL...

THANKS IN ADVANCE,

Re: SAS ENTERPRISE GUIDE: TRAFFIC LIGHTING - REPORT WITH COLORS ON SUB-SET OF DATA

Vince_SAS — Sat, 05 Aug 2017 23:30:42 GMT

Are you using PROC REPORT to create the output? If so, then here is an example:

Example 13: Specifying Style Elements for ODS Output in Multiple Statements

http://support.sas.com/documentation/cdl/en/proc/70377/HTML/default/viewer.htm#p0xcdcilo2yuuwn1t9uks2c1e66e.htm

This paper provides examples using PROC PRINT and PROC report, the the techniques should work work with HTML, RTF, and PDF output, not only Excel:

Traffic Lighting Your Multi-Sheet Microsoft Excel Workbooks the Easy Way with SAS®

http://support.sas.com/resources/papers/proceedings10/153-2010.pdf

Vince DelGobbo

SAS R&D

Re: SAS ENTERPRISE GUIDE: TRAFFIC LIGHTING - REPORT WITH COLORS ON SUB-SET OF DATA

PPimentel_Chile — Mon, 14 Aug 2017 12:52:23 GMT

Hi Vince,

I´m only using sorts, query builder and summary tables of enterprise guide

i´ve still with the problem unsolved, i can identify the 2 smallest values of each sub-set of data (7 values) from the column, but i still cannot apply the colors to these values

I want the output in html or pdf, the print in excel was only an example

proc report data=WORK.'QUERY_FOR_PROFIT'n nowd;

column DATE cv1 COMPANY 'Fondo 1'n, SUM='Fondo 1_SUM'n 'Fondo 2'n, SUM='Fondo 2_SUM'n 'Fondo 3'n, SUM='Fondo 3_SUM'n 'Fondo 4'n, SUM='Fondo 4_SUM'n 'Fondo 5'n, SUM='Fondo 5_SUM'n;

define DATE / group format=DATE9. missing noprint order=internal;

define cv1 / computed 'DATE' format=DATE9. missing;

compute cv1;

if DATE ne . then hold1=DATE;

cv1=hold1;

endcomp;

define COMPANY / group 'COMPANY' format=$CHAR9. missing order=formatted;

compute COMPANY;

if COMPANY ne ' ' then hold2=COMPANY;

if COMPANY eq ' ' then COMPANY=hold2;

endcomp;

define 'Fondo 1'n / analysis SUM 'Fondo 1' missing;

define 'Fondo 2'n / analysis SUM 'Fondo 2' missing;

define 'Fondo 3'n / analysis SUM 'Fondo 3' missing;

define 'Fondo 4'n / analysis SUM 'Fondo 4' missing;

define 'Fondo 5'n / analysis SUM 'Fondo 5' missing;

run;

quit;

Re: SAS ENTERPRISE GUIDE: TRAFFIC LIGHTING - REPORT WITH COLORS ON SUB-SET OF DATA

Vince_SAS — Tue, 15 Aug 2017 12:31:26 GMT

The techniques in the references that I provided earliler work for HTML and PDF.

The sample program below shows provides an example of applying style override code to one column in your data.

options validvarname=any;

data work.query_for_profit;
length DATE 8 COMPANY $3 'Fondo 1'n-'Fondo 5'n 8;
input DATE date9. COMPANY 'Fondo 1'n-'Fondo 5'n;
format DATE date9.;
cards;
31JUL2017 C-1 4.47 2.89 3.79 4.52 4.63
31JUL2017 C-2 3.50 3.50 3.50 3.50 3.98
31JUL2017 C-3 4.62 4.62 4.62 4.62 7.33
31JUL2017 C-4 2.47 3.47 3.47 3.15 7.10
;
run;

ods _all_ close;

ods html file='C:\temp\temp.htm';
ods pdf  file='C:\temp\temp.pdf';

title;
footnote;

proc report data=work.query_for_profit nowd;
  column DATE cv1 COMPANY 
         'Fondo 1'n, SUM='Fondo 1_SUM'n 
         'Fondo 2'n, SUM='Fondo 2_SUM'n 
         'Fondo 3'n, SUM='Fondo 3_SUM'n 
         'Fondo 4'n, SUM='Fondo 4_SUM'n 
         'Fondo 5'n, SUM='Fondo 5_SUM'n
         temp;

  define DATE       / group format=DATE9. missing noprint order=internal;
  define cv1        / computed 'DATE' format=date9. missing;
  define COMPANY    / group 'COMPANY' format=$char9. missing order=formatted;
  define 'Fondo 1'n / analysis SUM 'Fondo 1' missing;
  define 'Fondo 2'n / analysis SUM 'Fondo 2' missing;
  define 'Fondo 3'n / analysis SUM 'Fondo 3' missing;
  define 'Fondo 4'n / analysis SUM 'Fondo 4' missing;
  define 'Fondo 5'n / analysis SUM 'Fondo 5' missing;
  define temp       / computed noprint;

  compute temp;
    *  Rules for Fondo 1_SUM here;
    if ('Fondo 1_SUM'n le 2.49)
      then call define('Fondo 1_SUM', 'style', 'style=[background=red]');
    else if ('Fondo 1_SUM'n le 3.50)
      then call define('Fondo 1_SUM', 'style', 'style=[background=yellow]');
      else call define('Fondo 1_SUM', 'style', 'style=[background=green]');

    *  Rules for Fondo 2_SUM here;

    *  Rules for Fondo 3_SUM here;

    *  Rules for Fondo 4_SUM here;

    *  Rules for Fondo 5_SUM here;

  endcomp;

  compute cv1;
    if DATE ne . then hold1=DATE;
    cv1=hold1;
  endcomp;

  compute COMPANY;
    if COMPANY ne ' ' then hold2=COMPANY;
    if COMPANY eq ' ' then COMPANY=hold2;
  endcomp;
run; quit;

ods _all_ close;

Vince DelGobbo

SAS R&D

Re: SAS ENTERPRISE GUIDE: TRAFFIC LIGHTING - REPORT WITH COLORS ON SUB-SET OF DATA

PPimentel_Chile — Tue, 15 Aug 2017 15:28:01 GMT

Hi Vince, thanks for your response,
We have historical data since 2010 of profits of the 7 companies
(C-1,C-2,..), each month with 5 different investment fund (Fondo). Each
month we identify the 10 smallest profits. Is there any way to
automatically apply the color to the lowest profit performance (smallest)?
Best regards,

Re: SAS ENTERPRISE GUIDE: TRAFFIC LIGHTING - REPORT WITH COLORS ON SUB-SET OF DATA

Vince_SAS — Tue, 15 Aug 2017 15:53:59 GMT

Perhaps pre-process the data, create global macro variables for each Fondo, and then use the macro variables in COMPUTE block that assigns the colors?

Vince DelGobbo

SAS R&D

Re: SAS ENTERPRISE GUIDE: TRAFFIC LIGHTING - REPORT WITH COLORS ON SUB-SET OF DATA

PPimentel_Chile — Tue, 15 Aug 2017 15:58:01 GMT

Could you provide us an example of that?
Thanks,

Re: SAS ENTERPRISE GUIDE: TRAFFIC LIGHTING - REPORT WITH COLORS ON SUB-SET OF DATA

Vince_SAS — Tue, 15 Aug 2017 16:06:55 GMT

No, I can't. It's something very specific to your data and to your data structure. You may be able to use PROC SORT and then keep the first 10 records.

You might try posting your data manipulation question in the Base SAS Programming forum:

https://communities.sas.com/t5/Base-SAS-Programming/bd-p/programming

Good luck!

Vince DelGobbo

SAS R&D

Re: SAS ENTERPRISE GUIDE: TRAFFIC LIGHTING - REPORT WITH COLORS ON SUB-SET OF DATA

PPimentel_Chile — Tue, 15 Aug 2017 17:49:01 GMT

someone resolved the original problem of the first post?

Re: SAS ENTERPRISE GUIDE: TRAFFIC LIGHTING - REPORT WITH COLORS ON SUB-SET OF DATA

ChrisHemedinger — Tue, 15 Aug 2017 18:15:19 GMT

I think @Vince_SAS provided a good solution for your traffic highlighting question, but then you snuck in a data manipulation question -- how to determine the boundaries of the highlights and which records fall into them.

You can use PROC RANK as one method to find/flag the "bottom 10" for a given group of records. Here's an example that uses sample data. In this sample, I'm highlighting the bottom 3 auto makes, ranked by MSRP.

/* Sort before ranking BY a group */
PROC SORT
	DATA=sashelp.cars
	OUT=WORK.bymake
	;
	BY Make Model;
RUN;

/* create a rank variable */
PROC RANK DATA = WORK.bymake
	TIES=MEAN
	OUT=WORK.rankedMsrp;
	BY Make;
	VAR MSRP;
RANKS rank_MSRP ;

/* Create a report that marks LOWEST 3 in each group in Red */
proc report data=work.rankedmsrp nowd;
	column make model msrp rank_MSRP temp;
	define make / display order;
	define model / display;
	define msrp / order;
	define temp / computed noprint;
	define rank_MSRP / noprint order;
	compute temp;
	 if (rank_MSRP <= 3)
	      then call define('MSRP', 'style', 'style=[background=red]');
	endcomp;
run; quit;

Result: