SAS character format based on string patterns.

[ErikLund_Jensen]

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;

[FreelanceReinh · Accepted solution]

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.

[ErikLund_Jensen]

Hi @FreelanceReinh 

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, 

[Tom]

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;