Special characters (such as the bullet or triangle) in Microsoft Excel might be displayed incorrectly as other characters (for example, a question mark) when you import them into SAS.

This issue occurs because the font used in Excel for these special characters is often a symbol font or the Wingdings font. These characters might be mapped to different positions in the font sets (for example, SAS Monospace or Courier) that SAS uses. Because the character positions are different, incorrect characters are displayed in SAS.

To circumvent the problem, do the following:

1. Submit the code on the Full Code tab to determine the hexadecimal positions of characters for your font. To see the font that is set in the output window, select Tools ► Options ► Fonts.
2. Use the following DATA step to translate the character from its location in the symbol font table to its position in the SAS font. In this particular example, the DATA step translates the bullet character in Excel's symbol font to the bullet character in the SAS Monospace and other fonts that are used in SAS. In the example, the hexadecimal address for the bullet is '3F'x in the symbol font, and the address is '07'x in the SAS Monospace font. However, you can substitute hexadecimal addressses for any special character.
   data newdata; set mydata; newvariable=translate(trim(oldvariable),'07'x,'3f'x); run;

Operating System and Release Information

Product Family	Product	System	SAS Release
			Reported	Fixed*
SAS System	SAS/ACCESS Interface to PC Files	Microsoft® Windows® for 64-Bit Itanium-based Systems	9.1 TS1M3
		Microsoft Windows Server 2003 Datacenter 64-bit Edition	9.1 TS1M3
		Microsoft Windows Server 2003 Enterprise 64-bit Edition	9.1 TS1M3
		Microsoft Windows XP 64-bit Edition	9.1 TS1M3
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3
		Microsoft Windows 2000 Datacenter Server	9.1 TS1M3
		Microsoft Windows 2000 Server	9.1 TS1M3
		Microsoft Windows 2000 Professional	9.1 TS1M3
		Microsoft Windows NT Workstation	9.1 TS1M3
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3
		Microsoft Windows XP Professional	9.1 TS1M3
		Windows Vista	9.1 TS1M3
		64-bit Enabled AIX	9.1 TS1M3
		64-bit Enabled HP-UX	9.1 TS1M3
		64-bit Enabled Solaris	9.1 TS1M3
		HP-UX IPF	9.1 TS1M3
		Linux	9.1 TS1M3
		Tru64 UNIX	9.1 TS1M3

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

This little sample generates all the hex locations for a particular font.

---

data _null_;
file print;
put '-----------0-------------';
put '00'x;
put '01'x;
put '02'x;
put '03'x;
put '04'x;
put '05'x;
put '06'x;
put '07'x;
put '08'x;
put '09'x;
put '0a'x;
put '0b'x;
put '0c'x;
put '0d'x;
put '0e'x;
put '0f'x;
put '-----------1-------------';
put '10'x;
put '11'x;
put '12'x;
put '13'x;
put '14'x;
put '15'x;
put '16'x;
put '17'x;
put '18'x;
put '19'x;
put '1a'x;
put '1b'x;
put '1c'x;
put '1d'x;
put '1e'x;
put '1f'x;
put '-----------2-------------';
put '20'x;
put '21'x;
put '22'x;
put '23'x;
put '24'x;
put '25'x;
put '26'x;
put '27'x;
put '28'x;
put '29'x;
put '2a'x;
put '2b'x;
put '2c'x;
put '2d'x;
put '2e'x;
put '2f'x;
put '-----------3-------------';
put '30'x;
put '31'x;
put '32'x;
put '33'x;
put '34'x;
put '35'x;
put '36'x;
put '37'x;
put '38'x;
put '39'x;
put '3a'x;
put '3b'x;
put '3c'x;
put '3d'x;
put '3e'x;
put '3f'x;
put '-----------4-------------';
put '40'x;
put '41'x;
put '42'x;
put '43'x;
put '44'x;
put '45'x;
put '46'x;
put '47'x;
put '48'x;
put '49'x;
put '4a'x;
put '4b'x;
put '4c'x;
put '4d'x;
put '4e'x;
put '4f'x;
put '-----------5-------------';
put '50'x;
put '51'x;
put '52'x;
put '53'x;
put '54'x;
put '55'x;
put '56'x;
put '57'x;
put '58'x;
put '59'x;
put '5a'x;
put '5b'x;
put '5c'x;
put '5d'x;
put '5e'x;
put '5f'x;
put '-----------6-------------';
put '60'x;
put '61'x;
put '62'x;
put '63'x;
put '64'x;
put '65'x;
put '66'x;
put '67'x;
put '68'x;
put '69'x;
put '6a'x;
put '6b'x;
put '6c'x;
put '6d'x;
put '6e'x;
put '6f'x;
put '-----------7-------------';
put '70'x;
put '71'x;
put '72'x;
put '73'x;
put '74'x;
put '75'x;
put '76'x;
put '77'x;
put '78'x;
put '79'x;
put '7a'x;
put '7b'x;
put '7c'x;
put '7d'x;
put '7e'x;
put '7f'x;
put '-----------8-------------';
put '80'x;
put '81'x;
put '82'x;
put '83'x;
put '84'x;
put '85'x;
put '86'x;
put '87'x;
put '88'x;
put '89'x;
put '8a'x;
put '8b'x;
put '8c'x;
put '8d'x;
put '8e'x;
put '8f'x;
put '-----------9-------------';
put '90'x;
put '91'x;
put '92'x;
put '93'x;
put '94'x;
put '95'x;
put '96'x;
put '97'x;
put '98'x;
put '99'x;
put '9a'x;
put '9b'x;
put '9c'x;
put '9d'x;
put '9e'x;
put '9f'x;
put '-----------a-------------';
put 'a0'x;
put 'a1'x;
put 'a2'x;
put 'a3'x;
put 'a4'x;
put 'a5'x;
put 'a6'x;
put 'a7'x;
put 'a8'x;
put 'a9'x;
put 'aa'x;
put 'ab'x;
put 'ac'x;
put 'ad'x;
put 'ae'x;
put 'af'x;
put '-----------b-------------';
put 'b0'x;
put 'b1'x;
put 'b2'x;
put 'b3'x;
put 'b4'x;
put 'b5'x;
put 'b6'x;
put 'b7'x;
put 'b8'x;
put 'b9'x;
put 'ba'x;
put 'bb'x;
put 'bc'x;
put 'bd'x;
put 'be'x;
put 'bf'x;
put '-----------c-------------';
put 'c0'x;
put 'c1'x;
put 'c2'x;
put 'c3'x;
put 'c4'x;
put 'c5'x;
put 'c6'x;
put 'c7'x;
put 'c8'x;
put 'c9'x;
put 'ca'x;
put 'cb'x;
put 'cc'x;
put 'cd'x;
put 'ce'x;
put 'cf'x;
put '-----------d-------------';
put 'd0'x;
put 'd1'x;
put 'd2'x;
put 'd3'x;
put 'd4'x;
put 'd5'x;
put 'd6'x;
put 'd7'x;
put 'd8'x;
put 'd9'x;
put 'da'x;
put 'db'x;
put 'dc'x;
put 'dd'x;
put 'de'x;
put 'df'x;
put '-----------e-------------';
put 'e0'x;
put 'e1'x;
put 'e2'x;
put 'e3'x;
put 'e4'x;
put 'e5'x;
put 'e6'x;
put 'e7'x;
put 'e8'x;
put 'e9'x;
put 'ea'x;
put 'eb'x;
put 'ec'x;
put 'ed'x;
put 'ee'x;
put 'ef'x;
put '-----------f-------------';
put 'f0'x;
put 'f1'x;
put 'f2'x;
put 'f3'x;
put 'f4'x;
put 'f5'x;
put 'f6'x;
put 'f7'x;
put 'f8'x;
put 'f9'x;
put 'fa'x;
put 'fb'x;
put 'fc'x;
put 'fd'x;
put 'fe'x;
put 'ff'x;
put '---------finished----------';
run;

When you import special characters (for example, a bullet character) from Microsoft Excel into the SAS System, the values might be remapped as question marks or other characters because of the different fonts that are used.

Type:	Usage Note
Priority:
Topic:	Data Management ==> Data Sources ==> External Databases ==> MS Excel

Date Modified:	2009-10-22 09:43:37
Date Created:	2009-05-14 10:02:49