Hi all
I have requestors who want SAS data sets exported to XLSX with cell background colors depending on data values. I have a data set variable with some 3500 different alphanumeric strings, which should have background-color set after the following rules:
1-3 alphabetic chars only : red.
3 alphabetic chars + 1 (digit): blue
all other : white.
I use a format as shown in the test code below, where the format is built from actual data with all input values to be set to red or blue listed. It is a working solution, but I would prefer a permanent format with defined rules independent on actual data. Unfortunately SAS does not support the following syntax. As fas as I know, SAS allows REGEXP in informats only, and it can only be used with other formats as labels, not constant values.
proc format;
value $bgcolnuv
'/\D{1,3}$/' (REGEXP) = 'cxffcccc'
'/\D{3}1$/' (REGEXP) = 'cxccccff'
other = 'cxffffff';
run;
But I need something to to the same, and I think there has to be a smarter solution than my "brute-force-format". So I hope someone knows of a way to define such a format.
Test code:
* sample data;
data have;
input Nuv $4.;
cards;
HG
AHR
CFG1
CBH3
BBB2
1217
1611
;
run;
* Format to set background color;
proc sql;
create table cval as
select distinct Nuv
from have;
quit;
data _null_; set cval end=eof;
if _N_ = 1 then call execute('proc format; value $bgcolnuv');
if prxmatch('/\D{1,3}$/',trim(Nuv)) then call execute(Nuv || '= "cxffcccc"');
else if prxmatch('/\D{3}1$/',trim(Nuv)) then call execute(Nuv || '= "cxccccff"');
if eof then call execute('other="cxffffff"; run;');
run;
ods listing close;
ods excel file="c:\temp\test.xlsx";
proc print data=have ;
var nuv / style={background=$bgcolnuv.};
run;
ods excel close;
ods listing;
Hi @ErikLund_Jensen,
You could define a format from a user-defined function where the function assigns the colors using PRXMATCH:
proc fcmp outlib=work.funcs.test;
function nuvcolor(s $) $8;
color=
if prxmatch('/\D{1,3}$/o',trim(s)) then 'cxffcccc'
else if prxmatch('/\D{3}1$/o',trim(s)) then 'cxccccff'
else 'cxffffff';
return(color);
endsub;
run;
options cmplib=work.funcs;
proc format;
value $bgcolnuv (default=8)
other=[nuvcolor()];
run;
data test;
set have;
c=put(Nuv, $bgcolnuv.);
run;
However, in my experience these "fancy" formats tend to cause unexpected errors or even crashes of a SAS session. Indeed, while developing the above code one SAS session crashed and once I got the below error message (from a similar test DATA step as above):
ERROR: An exception has been encountered. Please contact technical support and provide them with the following traceback information: The SAS task name is [DATASTEP] ERROR: Read Access Violation DATASTEP Exception occurred at (0BEADE59) Task Traceback Address Frame (DBGHELP API Version 4.0 rev 5) 000000000BEADE59 0000000006F3E150 sasdsxp:tkvercn1+0xFCE19 000000000BE77C7E 0000000006F3E1A0 sasdsxp:tkvercn1+0xC6C3E 000000000BDB1914 0000000006F3E560 sasdsxp:tkvercn1+0x8D4 000000000B531187 0000000006F3E568 uwuprxma:tkvercn1+0x147
So, I would be hesitant to use this in production code.
Hi @ErikLund_Jensen,
You could define a format from a user-defined function where the function assigns the colors using PRXMATCH:
proc fcmp outlib=work.funcs.test;
function nuvcolor(s $) $8;
color=
if prxmatch('/\D{1,3}$/o',trim(s)) then 'cxffcccc'
else if prxmatch('/\D{3}1$/o',trim(s)) then 'cxccccff'
else 'cxffffff';
return(color);
endsub;
run;
options cmplib=work.funcs;
proc format;
value $bgcolnuv (default=8)
other=[nuvcolor()];
run;
data test;
set have;
c=put(Nuv, $bgcolnuv.);
run;
However, in my experience these "fancy" formats tend to cause unexpected errors or even crashes of a SAS session. Indeed, while developing the above code one SAS session crashed and once I got the below error message (from a similar test DATA step as above):
ERROR: An exception has been encountered. Please contact technical support and provide them with the following traceback information: The SAS task name is [DATASTEP] ERROR: Read Access Violation DATASTEP Exception occurred at (0BEADE59) Task Traceback Address Frame (DBGHELP API Version 4.0 rev 5) 000000000BEADE59 0000000006F3E150 sasdsxp:tkvercn1+0xFCE19 000000000BE77C7E 0000000006F3E1A0 sasdsxp:tkvercn1+0xC6C3E 000000000BDB1914 0000000006F3E560 sasdsxp:tkvercn1+0x8D4 000000000B531187 0000000006F3E568 uwuprxma:tkvercn1+0x147
So, I would be hesitant to use this in production code.
So there was another solution. I wasn't aware that a function could be used in a format.
Your code didn't crash when I tried it on my Windows laptop or linux Grid, but as it seems to do so now an then, I will follow your advise and not use it in a production environment, so I will continue to use my temporary format, even if it seems a clumsy solution. It also spares me from documenting and maintaining the extra function and format job.
I am not finished with this use of functions. I have a similar problem with overdue dates, and maybe it will be more stable with numeric arguments and aritmethics only, so I am very grateful that you took the time to write that program for me,
You can define a format from data using the CNTLIN= option on PROC FORMAT instead of generating lines of code.
proc sql;
create table NUV_FMT as
select distinct
'$BGCOLNUV' as fmtname
,nuv as start
,case when prxmatch('/\D{1,3}$/',trim(Nuv)) then 'cxffcccc'
when prxmatch('/\D{3}1$/',trim(Nuv)) then 'cxccccff'
else 'cxffffff'
end as label
from have
order by 1,2
;
quit;
proc format cntlin=nuv_fmt;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.