topic Re: Unicode characters in Proc Report in SAS Programming

Unicode characters in Proc Report

RandoDando — Wed, 28 Oct 2020 12:05:51 GMT

I am using Proc Report to create a list of counties grouped by a date, showing an average and change indicator (which indicates the change from the average at the prior date). "Change" is already computed, has values -1 (down), 1 (Up), 0 (no change). Below is my code. This shows the correct format and everything I want to see, except I want an up/down arrow for 1 / -1 and perhaps a horizontal dash for 0. I believe unicode characters would be most efficient. Any idea on how to implement those for the "Change" columns in the report? Will these export to Excel?

proc report data=change2 nowd;
columns  ('State Postal Code' state_cd) ('State Name' state)  county FIPS DATE, (average change)  ;

define state_cd /  group  ;
define state /  group  order=internal;
define county/County Name" group left order=internal;
define FIPS/ group ;
define Date/ across order =  data;
define average /   center  style(column)={background=colorraw.};
define change /  center   ;
COMPUTE state_cd;
      IF state_Cd NE "" THEN DUMMY=State_cd;
      ELSE State_cd=DUMMY;
       endcomp;
  COMPUTE state;
      IF state NE "" THEN DUMMY1=State;
      ELSE State=DUMMY1;
       endcomp;
run;quit;

Re: Unicode characters in Proc Report

Ksharp — Wed, 28 Oct 2020 13:19:42 GMT

This could give you a start. Find the right unicode for arrow.
data have;
set sashelp.class;
if sex='F' then arrow=unicode('\u2264');
else arrow=unicode('\u2265');
run;
proc print;run;

Re: Unicode characters in Proc Report

RandoDando — Wed, 28 Oct 2020 13:48:48 GMT

After searching the character map and some other posts here, I came up with this solution using Proc Format. The characters are displaying correctly in the output (haven't yet tested the export to Excel), but the conditional formatting in the compute statement isn't working. I get a warning message stating Change is uninitialized. Not sure why...


proc format;
value colorchngu
1  = "(*ESC*){unicode '25b2'x}"
-1 = "(*ESC*){unicode '25bc'x}"
0 = "(*ESC*){unicode '25ac'x}"
;
run;


proc report data=change2 nowd;
columns  ('State Postal Code' state_cd) ('State Name' state)  county FIPS DATE, (average change )  ;
define state_cd / 'group    ;
define state / 'group  order=internal;
define county/ group left order=internal;
define FIPS/'' group ;
define date/ across order =  data;
define average / center  style(column)={background=colorraw.};
define change/  center format=colorchngu. ;

compute change;
if change = 1 then do;
call define(_col_,'style','Style={font_size=24pt font_color=VIYPK}');
end;
if change = -1 then do;
call define(_col_,'style','Style={font_size=24pt font_color=BILG}');
end;
endcomp;


COMPUTE state_cd;
      IF state_Cd NE "" THEN DUMMY=State_cd;
      ELSE State_cd=DUMMY;
       endcomp;
  COMPUTE state;
      IF state NE "" THEN DUMMY1=State;
      ELSE State=DUMMY1;
       endcomp;
run;quit;

Re: Unicode characters in Proc Report

Ksharp — Thu, 29 Oct 2020 11:07:12 GMT

That means your dataset don't contain 'change' variable .

Re: Unicode characters in Proc Report

RandoDando — Thu, 29 Oct 2020 11:17:57 GMT

It's definitely there, and it's in the columns statement.

FWIW This message does not show as a warning, but as a note.

Re: Unicode characters in Proc Report

Ksharp — Thu, 29 Oct 2020 11:18:08 GMT

data have;
set sashelp.class;
if sex='F' then arrow=unicode('\u25b2');
else arrow=unicode('\u25bc');
run;
proc print;run; 



/***********************/
ods escapechar='~';
proc report data=sashelp.class nowd;
columns name sex age arrow;
define name/display;
define arrow/computed ;
compute arrow/character length=40;
if sex='F' then do;
 arrow='~{unicode 25b2}';
 call define(_col_,'style','style={foreground=red}');
end;
else do;
 arrow='~{unicode 25bc}';
 call define(_col_,'style','style={foreground=green}');
end;
endcomp;
run;

Re: Unicode characters in Proc Report

Ksharp — Thu, 29 Oct 2020 11:25:27 GMT

1) the first way: you need DISPLAY to refer to 'change':
define change/ display center format=colorchngu. ;

2)the second way : your code imply 'change' is ANALYSIS usage.
compute change;
if change.SUM = 1 then do;
...
if change.SUM = -1 then do;

Re: Unicode characters in Proc Report

RandoDando — Thu, 29 Oct 2020 11:44:18 GMT

I tried both of those, and I still cannot get the arrows to appear at all. The code I posted above works for displaying the arrows, but without the conditional formatting. Is it possible to run a compute on a variable which is being grouped by an across variable (in this case "date")?

Re: Unicode characters in Proc Report

Ksharp — Thu, 29 Oct 2020 11:56:14 GMT

Can you post some sample data to test your code ?

Re: Unicode characters in Proc Report

RandoDando — Thu, 29 Oct 2020 12:13:43 GMT

I got it to work. Finally. Instead of placing the conditional formatting in a compute, I used an additional PROC FORMAT statement for just the colors and specified that format in a style statement.


proc format;
value colorchngu
1  = "(*ESC*){unicode '25b2'x}"
-1 = "(*ESC*){unicode '25bc'x}"
0 = "(*ESC*){unicode '25ac'x}"
;
run;

proc format;
value coloru
1  = 'VIYPK'
-1 = 'BILG'
0 = 'Black';
run;

proc report data=change2 nowd;
columns  ('State Postal Code' state_cd) ('State Name' state)  county FIPS DATE, (average change )  ;
define state_cd / group    ;
define state / group  order=internal;
define county/ group left order=internal;
define FIPS/ group ;
define date/ across order =  data;
define average / center  style(column)={background=colorraw.};
define change/  center format=colorchngu  style={foreground=coloru.} ;

COMPUTE state_cd;
      IF state_Cd NE "" THEN DUMMY=State_cd;
      ELSE State_cd=DUMMY;
       endcomp;
  COMPUTE state;
      IF state NE "" THEN DUMMY1=State;
      ELSE State=DUMMY1;
       endcomp;
run;quit;