BookmarkSubscribeRSS Feed
PPimentel_Chile
Calcite | Level 5

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

 

report.jpg

THANKS IN ADVANCE,

9 REPLIES 9
Vince_SAS
Rhodochrosite | Level 12

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

 

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

PPimentel_Chile
Calcite | Level 5

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;

Vince_SAS
Rhodochrosite | Level 12

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

 

PPimentel_Chile
Calcite | Level 5
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,


Vince_SAS
Rhodochrosite | Level 12

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

 

PPimentel_Chile
Calcite | Level 5
Could you provide us an example of that?
Thanks,
Vince_SAS
Rhodochrosite | Level 12

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

PPimentel_Chile
Calcite | Level 5
someone resolved the original problem of the first post?
ChrisHemedinger
Community Manager

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:

lowrank.png

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 9 replies
  • 1150 views
  • 1 like
  • 3 in conversation