BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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.
16 REPLIES 16
Cynthia_sas
SAS Super FREQ
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
SASPhile
Quartz | Level 8
Hi,
all the rows .in this case 1002,both rows to be color coded.
polingjw
Quartz | Level 8
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
SASPhile
Quartz | Level 8
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
polingjw
Quartz | Level 8
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?
SASPhile
Quartz | Level 8
all four phyid’s to have their own distinct color
1002 light yellow
1003 light red
1005 light brown
1006 light orange
data_null__
Jade | Level 19
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]
SASPhile
Quartz | Level 8
Thats true.But the grouping is done on bunch of variables not just phyid.in such situations proc sql is more handy.
polingjw
Quartz | Level 8
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.
data_null__
Jade | Level 19
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.
Cynthia_sas
SAS Super FREQ
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]
SASPhile
Quartz | Level 8
Thanks Cynthia.Trying to see if alternating colors can be assigned if the phyid's are adjacent to each other.
Cynthia_sas
SAS Super FREQ
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]
polingjw
Quartz | Level 8
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?

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 16 replies
  • 1471 views
  • 0 likes
  • 4 in conversation