@eawh100 wrote:
Hi! Thanks for this. So my dataset was a short version of what I would be working with, so lots more options may appear in the DESC columns. I feel like making a format for a longer dataset would be quite time consuming? But this is helpful to know.
Once you learn that Proc Format will use a data set with certain special variables maybe not.
data work.have;
input id $ INITIALS $ code var2 $ START_DATE :Date9. DESC $;
format start_date date9.;
datalines;
a GT 1 na 17Nov2023 h
a GT 1 na 17Nov2023 h
a GT 1 na 17Nov2023 c
a GT 1 na 19Nov2023 c
b hi 1 . 22Nov2023 d
b hi 1 . 10Nov2023 d
b hi 1 . 10Nov2023 d
a GT 1 . 10Nov2023 f
;
Proc sql;
create table work.desc as
select distinct desc
from work.have
;
run;
data work.make_desc_format;
set work.desc (rename=(desc=start)) nobs=ob;
r = floor( ( _n_/ob)*255);
b = mod(r+25,255);
g = mod(r+100,255);
fmtname= "dfmt";
type='C';
label=cats("CX",put(r,Hex2.),put(g,Hex2.),put(b,Hex2.));
run;
proc format library=work cntlin=work.make_desc_format;
run;
proc report data=work.have;
columns id INITIALS code var2 START_DATE DESC;
define id / display;
define initials / display;
define code / display;
define var2 / display;
define start_date /display;
define desc / display;
compute desc;
call define (_row_,"style","style=[backgroundcolor="||put(desc,$dfmt.)||"]");
endcomp;
run;
What the above does.
First get a set with the unique values of Desc.
Then add variables to allow creating a format similar to the one @Cynthia_sas wrote by hand.
The key variables are FMTNAME, TYPE C or N for character numeric, START (the values of the variable) and Label what to display for the value of Start.
I used an RGB color scheme which uses a string value of CX followed by 3 Hex values of 0 to 255. The OB variable from the NOBS option allows creating a ratio of the current observation number, _n_ and the number of observations in the data set to distribute the values into ranges. The *255 turns the ratio into a number in the range of R (G or B) values of 0 to 255. The + 25 and +100 combined with the MOD function return "scattered" values of G and B for a given R. If all the R, G and B are the same then you get a gray shading scale.
Note that depending on the number of values of DESC you may end up with RGB results that are too dark to read the values. Other naming schemes are available HSV, HLS, CMYK that could use different ranges of ratio and range calculations for the color.
Or the SAS registry has the defined named colors you could pull such as with Proc Surveyselect to get a random set of names and match to your Start variable values.
The online help on Color-Naming Schemes has much helpful information for creating valid color values.
... View more