BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mmohotsi
Obsidian | Level 7

Dear colleagues

 

I need to create a sas dataset from this Spreadsheet below. I have challenges showing these symbols in SAS.

 

Soccer  ≤20 years

Netball ≥ 20 years

Pool   15≤ years≤ 25

 

Regards

Mighty

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

Start SAS in the "unicode" mode. Essentially it is using the UTF8 encoding. This will enable you to read these characters from Excel or other data sources sucessfully and display them in this way. For instance, the SAS University Edition is using the unicode mode by default.

 

If you want to display these types of symbols in non unicode SAS session, you can use the Inline Formatting function {unicode }.

To make things easier, you could create a format that will create the necessary inline formatting string, see example below. The example uses the "(*ESC*)" escape sequence, so it is independant of the ODS ESCAPECHAR setting.

 

proc format;
  value $mysigns
    "LE" = "(*ESC*){unicode 2264}"
    "GE" = "(*ESC*){unicode 2265}"
  ;
run;

data want;
  newValue = catx("*", put("LE", $mysigns.), put("GE", $mysigns.));
run;

proc print data=want;
run;

Bruno

View solution in original post

7 REPLIES 7
Shmuel
Garnet | Level 18

Depending on your sas platform, do you know the ascii code of those symbols ?

If yes define: char = byte(<ascii_code>)  or char = 'xx'X where xx is the hexadecimal presentation of the ascii code;

chrej5am
Quartz | Level 8

The problem is that most likely your session does not support lesser equal symbol as on of the possible characters and will show as bracket or possibly something else.

If you have only the lesser equal sign you can have it replaced by something else like string 'leq' or 'geq'.

 

The bracket sign number can be found by RANK() function and recalled by BYTE() with the number as the argument. Then you can replace it by something else like

tranwrd(var1,byte(number_from_ascii_tab),'*');

 

Nevertheless this will replace all the characters with the star, so I would suggest to do the replacement either in excel or start different SAS session with other encoding supported.

ballardw
Super User

HOW do you need to use these? Are you actually comparing values some where or is this like a range response to a question in a survey or simply text you need to display? Note that many characters can be Font specific unless you are using UNICODE and then you may still get to consider the number of bytes used for each character.

 

mmohotsi
Obsidian | Level 7
Good evening

Thanks you for your response.
I only need to display these in a report.

Regards
Mighty
ballardw
Super User

If you can read the data okay as a text field then your solution may be to specify the correct font for reports or such. That might entail creating a custom ODS Style template.

 

If the data is actually UNICODE that should take of itself if you can read it. When you attempt to read the data do you get any message in the log about encoding?

rogerjdeangelis
Barite | Level 11
Importing special characters in EXCEL XML sheets
and creatting a HTM, PDF or RTF report
with the special characters.

There is probably an easier solution but this might
lead to a better solution.

HAVE an EXCEL Sheet with synmbols for
'less than or equal'. When I import to
SAS the symbols are gone.


HAVE
====

If you view the sheet, It looks
fine witht the less than or equal
as one character. I had to use
two lines because I like clear text.


  +------------------------------+
  |            A                 |
  +------------------------------+
1 | Pool 15 < year < 25          |
  |         -      -             |
  +------------------------------+

WANT an HTML, RTF or PDF
that looks like the Excel file
================================

  +------------------------------+
  |     Less than or equal       |
  +------------------------------+
1 | Pool 15 < year < 25          |
  |         -      -             |
  +------------------------------+

But I get

  +------------------------------+
  |     Less than or equal       |
  +------------------------------+
1 | Pool 15 = year = 25          |
  |                              |
  +------------------------------+


WORKING CODE
============

read.xlsx("d:/xls/symbols.xlsx"
         ,1
          ,colClasses=c("character")
         ,stringsAsFactors=FALSE
          ,encoding="UTF-16");


FULL SOLUTION
=============

* You can use proc IML and submit/R to do this;
%utl_submit_r64('
library(xlsx);
x<-read.xlsx("d:/xls/symbols.xlsx",1,colClasses=c("character"),stringsAsFactors=FALSE,encoding="UTF-16");
str(x);
x$symbols;
for (i in 1:3) {
write(x$symbols[i], file = "d:/txt/symbols.txt",sep="\n");
};
');

* Convert 'lesss than or equal' in UTF-16 to unicode;
* also get rid of the non breaking space;

data fix;
 length le $64;
 infile "d:/txt/symbols.txt";
 input;
 * remove the non-breaking space;
 _infile_=tranwrd(_infile_,'C2A0C2A0'x,'33'x);
 _infile_=tranwrd(_infile_,'E289A4'x,'^{unicode 2264}');
 put _infile_;
 put _infile_ $hex96.;
 le=_infile_;
run;quit;

* create html file with symbols;
ods escapechar='^';
ods html file="d:/htm/le.htm";
proc report data=fix(obs=1) nowd;
cols le;
define le /display style=[asis=on];
run;quit;
ods html close;

DETAILS
=======

It is difficult to provide an exact solution
because I cannot see exactly what is in the excel
cell in hex without yout spreadsheet..
I am going to assume your
excel data is encoded at UTL-16.

see
http://graphemica.com/%E2%89%A4


I pasted your code into excel and then used
R and UTF-16 encoding to create a text file.
Note this should be possible in SAS, you
should be able to set the encoding.

  +------------------------------+
  |            A                 |
  +------------------------------+
1 | Pool   15≤ years≤ 25   |
  +------------------------------+

Here is what it looks like in hex

 506F6F6CC2A0C2A0203135E289A4207965617273E289A4203235

Note E289A4 is UTF-16 for 'less than or equal'

       Non Breaking Space
          NBSP NBSP         LE                   LE
 506F6F6C C2A0 C2A0 203135 E289A4 207965617273 E289A4 203235

Now lets create a report in html with the le symbol

HTML Entity (Named)         &le;
HTML Entity (Decimal)       &#8804;
HTML Entity (Hexadecimal)   &#x2264;
SAS                         ^{unicode 2264}

BrunoMueller
SAS Super FREQ

Start SAS in the "unicode" mode. Essentially it is using the UTF8 encoding. This will enable you to read these characters from Excel or other data sources sucessfully and display them in this way. For instance, the SAS University Edition is using the unicode mode by default.

 

If you want to display these types of symbols in non unicode SAS session, you can use the Inline Formatting function {unicode }.

To make things easier, you could create a format that will create the necessary inline formatting string, see example below. The example uses the "(*ESC*)" escape sequence, so it is independant of the ODS ESCAPECHAR setting.

 

proc format;
  value $mysigns
    "LE" = "(*ESC*){unicode 2264}"
    "GE" = "(*ESC*){unicode 2265}"
  ;
run;

data want;
  newValue = catx("*", put("LE", $mysigns.), put("GE", $mysigns.));
run;

proc print data=want;
run;

Bruno

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 9748 views
  • 1 like
  • 6 in conversation