Hi there,

For numeric var, we can define range in format to control the
cell background/foreground. What about character var?

data xx;
s='123523'; output;
s='   234'; output;
s='Abc dd'; output;
run;

ods pdf file="c:\temp\junk.pdf" style=printer;

proc format;
value $msbg
low-high='white'
other='black'
;

options missing='';
proc print data=xx;
var s / style=[background=$msbg.];
run;

ods pdf close;

My goal is to make s black background, if s value includes any letter
other than numbers (0-9). For the above data, obs=3 has value 'Abc dd',
which is not all numnbers, therefore, this cell need to be black.

Maybe picture format?

Thanks

Ya

On Fri, 5 Mar 2010 18:23:47 -0500, Ya Huang <ya.huang@AMYLIN.COM> wrote:

>Hi there,
>
>For numeric var, we can define range in format to control the
>cell background/foreground. What about character var?
>
>data xx;
>s='123523'; output;
>s='   234'; output;
>s='Abc dd'; output;
>run;
>
>ods pdf file="c:\temp\junk.pdf" style=printer;
>
>proc format;
>value $msbg
>low-high='white'
>other='black'
>;
>
>options missing='';
>proc print data=xx;
>var s / style=[background=$msbg.];
>run;
>
>ods pdf close;
>
>My goal is to make s black background, if s value includes any letter
>other than numbers (0-9). For the above data, obs=3 has value 'Abc dd',
>which is not all numnbers, therefore, this cell need to be black.
>
>Maybe picture format?
>
>Thanks
>
>Ya


Ya,

You can use conditional logic with functions and CALL DEFINE in PROC REPORT
to traffic-light character fields.

data x ;
  r=1 ;
  s='12345' ; output ;
  s='a 245' ; output ;
  s='   a9' ; output ;
  run ;

ods pdf file='sup.pdf' style=printer ;

   proc report data=x nowd ;
      column r s ;
      compute s ;
         if anyalpha( s )>0 then
            call define( _col_, 'style', 'style=[background=black]' ) ;
      endcomp ;
      run ;

ods pdf close ;

Regards,
Ken Borowiak

Ya,

Since you only want to black out variables that meet the condition,
couldn't you just use something like:

data xx;
  s='123523'; output;
  s='   234'; output;
  s='Abc dd'; output;
run;

ods pdf file="k:\art\junk.pdf" style=printer;

proc format;
  value $msbg
  'xxx'='black'
  other='white'
;
run;

data yy;
  set xx;
  length new_s $8.;
  if anyalpha(s) > 0 then new_s='xxx';
  else new_s=s;
run;

options missing='';
proc print data=yy;
  var new_s/ style=[background=$msbg.];
run;

ods pdf close;

Art
--------
On Fri, 5 Mar 2010 18:23:47 -0500, Ya Huang <ya.huang@AMYLIN.COM> wrote:

>Hi there,
>
>For numeric var, we can define range in format to control the
>cell background/foreground. What about character var?
>
>data xx;
>s='123523'; output;
>s='   234'; output;
>s='Abc dd'; output;
>run;
>
>ods pdf file="c:\temp\junk.pdf" style=printer;
>
>proc format;
>value $msbg
>low-high='white'
>other='black'
>;
>
>options missing='';
>proc print data=xx;
>var s / style=[background=$msbg.];
>run;
>
>ods pdf close;
>
>My goal is to make s black background, if s value includes any letter
>other than numbers (0-9). For the above data, obs=3 has value 'Abc dd',
>which is not all numnbers, therefore, this cell need to be black.
>
>Maybe picture format?
>
>Thanks
>
>Ya

Ken,

This is great! Thanks.

Art,

Your solution also works. I do not only want to black out the cell,
actually what I want is to flip the background and foreground. A small
change to your code will be fine:

proc print data=yy;
  var new_s/ style=[background=$msbg. foreground=$msfg.];
run;

where $msfg. is a flip of $msbg.

So, thanks to both of you.

Ya

-----Original Message-----
From: Ken Borowiak [mailto:evilpettingzoo97@AOL.COM]
Sent: Friday, March 05, 2010 8:16 PM
To: SAS-L@LISTSERV.UGA.EDU; Huang, Ya
Cc: Ken Borowiak
Subject: Re: Traffic lighting for character variables?

On Fri, 5 Mar 2010 18:23:47 -0500, Ya Huang <ya.huang@AMYLIN.COM> wrote:

>Hi there,
>
>For numeric var, we can define range in format to control the cell
>background/foreground. What about character var?
>
>data xx;
>s='123523'; output;
>s='   234'; output;
>s='Abc dd'; output;
>run;
>
>ods pdf file="c:\temp\junk.pdf" style=printer;
>
>proc format;
>value $msbg
>low-high='white'
>other='black'
>;
>
>options missing='';
>proc print data=xx;
>var s / style=[background=$msbg.];
>run;
>
>ods pdf close;
>
>My goal is to make s black background, if s value includes any letter
>other than numbers (0-9). For the above data, obs=3 has value 'Abc dd',

>which is not all numnbers, therefore, this cell need to be black.
>
>Maybe picture format?
>
>Thanks
>
>Ya


Ya,

You can use conditional logic with functions and CALL DEFINE in PROC
REPORT to traffic-light character fields.

data x ;
  r=1 ;
  s='12345' ; output ;
  s='a 245' ; output ;
  s='   a9' ; output ;
  run ;

ods pdf file='sup.pdf' style=printer ;

   proc report data=x nowd ;
      column r s ;
      compute s ;
         if anyalpha( s )>0 then
            call define( _col_, 'style', 'style=[background=black]' ) ;
      endcomp ;
      run ;

ods pdf close ;

Regards,
Ken Borowiak

Art,

Sorry, looks like I was wrong about yours solution. Since I want the
original value of the cell still be shown, just to flip the background
and foreground. The way of making it 'xxx' will lost the original
value information.

Ken's method, with the call define, the style
is applied on the fly based on the cell value, so it works fine.

Ya


-----Original Message-----
From: Huang, Ya
Sent: Monday, March 08, 2010 8:17 AM
To: 'Ken Borowiak'; SAS-L@LISTSERV.UGA.EDU
Cc: 'Arthur Tabachneck'
Subject: RE: Re: Traffic lighting for character variables?

Ken,

This is great! Thanks.

Art,

Your solution also works. I do not only want to black out the cell,
actually what I want is to flip the background and foreground. A small
change to your code will be fine:

proc print data=yy;
  var new_s/ style=[background=$msbg. foreground=$msfg.]; run;

where $msfg. is a flip of $msbg.

So, thanks to both of you.

Ya

-----Original Message-----
From: Ken Borowiak [mailto:evilpettingzoo97@AOL.COM]
Sent: Friday, March 05, 2010 8:16 PM
To: SAS-L@LISTSERV.UGA.EDU; Huang, Ya
Cc: Ken Borowiak
Subject: Re: Traffic lighting for character variables?

On Fri, 5 Mar 2010 18:23:47 -0500, Ya Huang <ya.huang@AMYLIN.COM> wrote:

>Hi there,
>
>For numeric var, we can define range in format to control the cell
>background/foreground. What about character var?
>
>data xx;
>s='123523'; output;
>s='   234'; output;
>s='Abc dd'; output;
>run;
>
>ods pdf file="c:\temp\junk.pdf" style=printer;
>
>proc format;
>value $msbg
>low-high='white'
>other='black'
>;
>
>options missing='';
>proc print data=xx;
>var s / style=[background=$msbg.];
>run;
>
>ods pdf close;
>
>My goal is to make s black background, if s value includes any letter
>other than numbers (0-9). For the above data, obs=3 has value 'Abc dd',

>which is not all numnbers, therefore, this cell need to be black.
>
>Maybe picture format?
>
>Thanks
>
>Ya


Ya,

You can use conditional logic with functions and CALL DEFINE in PROC
REPORT to traffic-light character fields.

data x ;
  r=1 ;
  s='12345' ; output ;
  s='a 245' ; output ;
  s='   a9' ; output ;
  run ;

ods pdf file='sup.pdf' style=printer ;

   proc report data=x nowd ;
      column r s ;
      compute s ;
         if anyalpha( s )>0 then
            call define( _col_, 'style', 'style=[background=black]' ) ;
      endcomp ;
      run ;

ods pdf close ;

Regards,
Ken Borowiak