topic Re: Proc report & Color code in SAS Programming

Proc report & Color code

SASPhile — Thu, 07 Oct 2010 15:11:39 GMT

phid name address qty
1001 John Philadephia 25
1002 Adams Tampa 30
1002 Adams Orlando 30
1003 Mark Austin 30

In proc report how to color code those phid which are not distinct.For instance 1002.The whole row need to be color coded.

Re: Proc report & Color code

Cynthia_sas — Thu, 07 Oct 2010 15:38:50 GMT

Hi:
You say that for 1002 the whole ROW needs to be color coded -- but I see 2 rows for 1002 -- so which row?? The first row?? The second row?? or both rows???

What if there are more than 2 rows??? would ALL the rows need to be updated?? Only the first row?? Everything but the first row???

cynthia

Re: Proc report & Color code

SASPhile — Thu, 07 Oct 2010 15:46:13 GMT

Hi,
all the rows .in this case 1002,both rows to be color coded.

Re: Proc report & Color code

polingjw — Thu, 07 Oct 2010 16:06:02 GMT

Will something like this work?

[pre]
DATA TEST;
LENGTH PHYID $ 4 NAME $ 10 ADDRESS $ 15;
INPUT PHYID $ NAME $ ADDRESS $ QTY;
DATALINES;
1001 John Philadephia 25
1002 Adams Tampa 30
1002 Adams Orlando 30
1003 Mark Austin 30
;
RUN;

/* CREATE MACRO VARIABLE WITH ALL DUPLICATE PHYID VALUES */
PROC SQL NOPRINT;
SELECT DISTINCT PHYID INTO: MULTIPLES SEPARATED BY '" "'
FROM
(SELECT PHYID, COUNT(*) AS COUNT
FROM TEST
GROUP BY PHYID
HAVING COUNT > 1);
QUIT;

ODS LISTING CLOSE;
ODS HTML FILE='TEST.HTML' STYLE=MINIMAL;

PROC REPORT DATA=TEST NOWINDOWS;
COLUMNS PHYID NAME ADDRESS QTY;
COMPUTE PHYID;
IF PHYID IN ("&MULTIPLES") THEN CALL DEFINE(_ROW_, "STYLE", "STYLE=[BACKGROUND=HONEYDEW]");
ENDCOMP;
RUN;
ODS HTML CLOSE;
ODS LISTING;
[/pre] Message was edited by: polingjw

Re: Proc report & Color code

SASPhile — Thu, 07 Oct 2010 16:22:35 GMT

Thanks,But this color codes only the first row.How to color code both the rows?and is there a way if there are more than one phyid which are not distinct,we can show them in different colors?

for instance two rows of 1002 is shown as honeydew.
say we have 1003 which has three rows can we display yellow?

1003 Mark Austin 30
1003 Mark Dallas 45
1003 Mark Elpaso 35

Re: Proc report & Color code

polingjw — Thu, 07 Oct 2010 16:57:39 GMT

When I run the program, both rows with a phyid of 1002 show up as honeydew.

Do you want a separate color for each phyid or a separate color for each frequency? For example, suppose 1002 and 1005 both have two records. Also, suppose 1003 and 1006 both have three records. Do you want 1002 and 1005 to have one color and 1003 and 1006 to have a different color? Or, do you want all four phyid’s to have their own distinct color?

Re: Proc report & Color code

SASPhile — Thu, 07 Oct 2010 17:09:31 GMT

all four phyid’s to have their own distinct color
1002 light yellow
1003 light red
1005 light brown
1006 light orange

Re: Proc report & Color code

data_null__ — Thu, 07 Oct 2010 17:16:45 GMT

If the list of duplicate IDs gets long the macro variable may become unwieldy. Consider an indicator variable.

[pre]
DATA TEST;
LENGTH PHYID $ 4 NAME $ 10 ADDRESS $ 15;
INPUT PHYID $ NAME $ ADDRESS $ QTY;
DATALINES;
1001 John Philadephia 25
1002 Adams Tampa 30
1002 Adams Orlando 30
1003 Mark Austin 30
1004 Adams Tampa 30
1004 Adams Orlando 30
1004 Mark Austin 30
;
RUN;

data test;
set test;
by phyid;
dup = not(first.phyid and last.phyid);
run;

ODS LISTING CLOSE;
ODS HTML FILE='TEST.HTML' STYLE=MINIMAL;

PROC REPORT DATA=TEST NOWINDOWS list;
COLUMNS dup PHYID NAME ADDRESS QTY;
define dup / noprint display;
define phyid / order;
COMPUTE PHYID;
/* rc = seenum(dup);*/
IF dup THEN CALL DEFINE(_ROW_, "STYLE", "STYLE=[BACKGROUND=HONEYDEW]");
ENDCOMP;
RUN;
ods HTML Close;
[/pre]

Re: Proc report & Color code

SASPhile — Thu, 07 Oct 2010 17:31:33 GMT

Thats true.But the grouping is done on bunch of variables not just phyid.in such situations proc sql is more handy.

Re: Proc report & Color code

polingjw — Thu, 07 Oct 2010 18:44:08 GMT

How about this solution? With this solution, you need to specify the colors that make acceptable backgrounds when calling a macro. You could probably write code to automatically generate a new color for each phyid. If you would like to do this instead, I would start by looking at the color macro utilities invoked by %COLORMAC. However, I would imagine that it might be difficult to automatically generate a list of colors that all look good as cell backgrounds.

[pre]
DATA TEST;
LENGTH PHYID $ 4 NAME $ 10 ADDRESS $ 15;
INPUT PHYID $ NAME $ ADDRESS $ QTY;
DATALINES;
1001 John Philadephia 25
1002 Adams Tampa 30
1002 Adams Orlando 30
1003 Mark Austin 30
1003 Mark Dallas 45
1003 Mark Elpaso 35
;
RUN;

%MACRO COLOR_REPORT(COLOR_LST);

%LOCAL MULTIPLES I PHYID COLOR;

/* CREATE MACRO VARIABLE WITH ALL DUPLICATE PHYID VALUES */
PROC SQL NOPRINT;
SELECT DISTINCT PHYID INTO: MULTIPLES SEPARATED BY ' '
FROM
(SELECT PHYID, COUNT(*) AS COUNT
FROM TEST
GROUP BY PHYID
HAVING COUNT > 1);
QUIT;

ODS LISTING CLOSE;
ODS HTML FILE='TEST.HTML' STYLE=MINIMAL;

PROC REPORT DATA=TEST NOWINDOWS;
COLUMNS PHYID NAME ADDRESS QTY;
COMPUTE PHYID;
%LET I=1;
%DO %UNTIL (%SCAN(&MULTIPLES, &I) =);
%LET PHYID = %SCAN(&MULTIPLES, &I);
%LET COLOR = %SCAN(&COLOR_LST, &I);
%IF &COLOR = %THEN %DO;
%PUT ERROR: NOT ENOUGH COLORS PROVIDED.;
ENDCOMP; RUN; ODS HTML CLOSE; ODS LISTING;
%RETURN;
%END;
IF PHYID = "&PHYID" THEN CALL DEFINE(_ROW_, "STYLE", "STYLE=[BACKGROUND=&COLOR]");
%LET I = %EVAL(&I+1);
%END;
ENDCOMP;
RUN;
ODS HTML CLOSE;
ODS LISTING;

%MEND;
%COLOR_REPORT(%STR(LIGHTYELLOW LIGHTRED LIGHTBROWN LIGHTORANGE))

[/pre]

I just saw _null_’s program and have not yet tried to modify mine accordingly. It definitely looks like that solution is superior if you can tweak it to make it work.

Re: Proc report & Color code

data_null__ — Thu, 07 Oct 2010 19:11:12 GMT

It would probably be easier to use a value label format for the colors. Then you can PUT the ID with the format to produce the color.

However, I question the OP's need for a different color for each ID. It seems like alternating between two colors would be adequate. Then if two adjacent IDs have dups they are easily distinguished. I could see having lots of IDs and you’re going to run out of colors.

Re: Proc report & Color code

SASPhile — Thu, 07 Oct 2010 19:40:40 GMT

As data _null_ suggested,alternating the colors is also a good idea.have to make changes on your code to see if that's possible.

Re: Proc report & Color code

Cynthia_sas — Thu, 07 Oct 2010 19:42:43 GMT

Hi:
There's an easier way with PROC REPORT. Just use the COMPUTE BEFORE PHID to "grab" and save the total number of rows for each PHID -- if you put N on the report as a NOPRINT item, you can test the saved value for each PHID -- no SQL or DATA step needed.

cynthia
[pre]
data phid;
infile datalines;
input phid name $ address $ qty;
return;
datalines;
1001 John Philadephia 25
1002 Adams Tampa 30
1002 Adams Orlando 30
1003 Mark Austin 30
;
run;

ods listing close;
ods html file='c:\temp\hilite_row.html' style=sasweb;
proc report data=phid nowd;
column phid name address qty n;
define phid / order;
define name / order;
define address / order;
define qty / sum;
define n / 'N' noprint;
compute before phid;
** grab the count of IDs and save in a temporary variable;
** the summary for the N statistic is available;
** at the break for COMPUTE BEFORE PHID;
totid = n;
endcomp;
compute phid;
** test the temporary variable TOTID for every PHID;
if totid gt 1 then do;
call define(_ROW_, 'style', 'style={background=pink}');
end;
endcomp;
run;
ods html close;
[/pre]

Re: Proc report & Color code

SASPhile — Thu, 07 Oct 2010 20:06:12 GMT

Thanks Cynthia.Trying to see if alternating colors can be assigned if the phyid's are adjacent to each other.

Re: Proc report & Color code

Cynthia_sas — Thu, 07 Oct 2010 21:45:23 GMT

Oh, well, somehow I missed that. If I understand what you mean, you want every different PHID to have a different color??? If so, I'd do it another way. See below. You can, of course, build the format list with a program.

cynthia

[pre]
** could build this format list dynamically from the data;
proc format;
value ph 1001='pink'
1002='yellow'
1003='cx66FFCC';
run;

ods listing close;
ods html file='c:\temp\alt_color1.html' style=sasweb;
proc report data=phid nowd;
title '1) With this Method, the N statistic is unnecessary';
column phid name address qty ;
define phid / order;
define testphid / computed noprint;
define name / order;
define address / order;
define qty / sum;
compute before phid;
** just grab and save the PHID value;
holdphid = phid;
endcomp;
compute testphid;
** create the TESTPHID which will be available on every ROW (but NOPRINT);
testphid = holdphid;
endcomp;
compute phid;
** use TESTPHID to set teh color of the STYVAL variable;
length styval $100;
styval = catt('style={background=',put(testphid,ph.),'}');
call define(_ROW_, 'style', styval);
endcomp;
run;
ods html close;
[/pre]

Re: Proc report & Color code

polingjw — Mon, 11 Oct 2010 18:22:18 GMT

Based on your last response, it sounds like you now want to just alternate between two colors instead of providing a different color for every duplicate phyid. If so, this can be accomplished by making some minor modifications to the program posted by _null_.

[pre]
DATA TEST;
LENGTH PHYID $ 4 NAME $ 10 ADDRESS $ 15;
INPUT PHYID $ NAME $ ADDRESS $ QTY;
DATALINES;
1001 John Philadephia 25
1002 Adams Tampa 30
1002 Adams Orlando 30
1003 Mark Austin 30
1004 Adams Tampa 30
1004 Adams Orlando 30
1004 Mark Austin 30
;
RUN;

data test(drop=alternate_dup);
set test;
by phyid;
retain alternate_dup 0;
dup = not(first.phyid and last.phyid);
if dup then do;
dup+alternate_dup;
if last.phyid and not alternate_dup then alternate_dup = 1;
else if last.phyid then alternate_dup = 0;
end;
run;

ODS LISTING CLOSE;
ODS HTML FILE='TEST.HTML' STYLE=MINIMAL;

PROC REPORT DATA=TEST NOWINDOWS list;
COLUMNS dup PHYID NAME ADDRESS QTY;
define dup / noprint display;
define phyid / order;
COMPUTE PHYID;
IF dup = 1 THEN CALL DEFINE(_ROW_, "STYLE", "STYLE=[BACKGROUND=HONEYDEW]");
IF dup = 2 THEN CALL DEFINE(_ROW_, "STYLE", "STYLE=[BACKGROUND=lightyellow]");
ENDCOMP;
RUN;
ods HTML Close;
[/pre]

Hopefully, either the report is not very long or having duplicates observations of the phyid variable is a rare event. Otherwise, I suspect that you might run into some performance issues. I remember that I once produced a long detail report using proc report and the RTF destination (about 100 legal size sheets of paper). To improve readability of the report, I tried to modify the background color on every other report row. After I had a working program, proc report took nearly an hour to run.

Not that it makes a difference now but, has anyone else had performance issues when doing similar tasks with proc report?

Re: Proc report & Color code

SASPhile — Mon, 11 Oct 2010 18:30:10 GMT

this looks good for now.i will let you if there is any performance issue should i run into any.