Importing Special Characters in SAS

[NehaChopra]

Hi, 

I have a study trial summary which have alpha and Gamma characters in the data. i am trying to import the xlsx file but it is excluding the special character. can some one please elp me with a code to import these special characters in into SAS format.

code i am using  :

filename name "C:\STATISTICS\ Trial Design_Specs.xlsx";

proc import datafile = name
out = ts1
dbms = xlsx
replace ;
sheet = 'TS' ;
run ;

data tss;
length STUDYID $200.
DOMAIN $200.
TSSEQ 8.
TSPARMCD $200.
TSPARM $200.
TSVAL $200. 
TSVALNF $200.
TSVALCD $200.
TSVALCD $200.
TSVCDREF $200.;
TSVCDVER $200. ;
set ts1;
format _ALL_;
informat _ALL_ ;
run;

data ts2 ;
set tss(where=(studyid ne '');
TSVAL=strip(tranwrd(TSVAL, '0D0A'x, ' '));
run;

I am getting the following warning in the log .

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with options MSGLEVEL=I.
WARNING: Some character data was lost during transcoding in column: TSVAL at obs 26.
NOTE: The import data set has 67 observations and 14 variables.
NOTE: WORK.TS1 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):

Thanks 

Neha 

[pink_poodle]

I would use Find/Replace in xcel to change those characters to "_gamma" and "_alpha."

[pink_poodle]

Here is another way:
https://communities.sas.com/t5/SAS-Programming/Import-data-from-MS-access-Excel-while-retaining-spec...

[NehaChopra]

i tried the above way as well but it is not working 

[SteveDenham]

I always get curious when someone replies that something is "not working".  How is it not working?  Be specific and provide any errors, warnings or specific NOTEs from the LOG and make sure that those have some context, especially if you are executing a macro.  Also, there may be messages in the output, depending on the PROCs involved.

SteveDenham

[NehaChopra]

Even after using the code i am getting this warning and looking at the output the characters are not converted and are missing ..

WARNING: Some character data was lost during transcoding in column: TSVAL at obs 26

thanks 

neha 

[NehaChopra]

that cannot be changed as that is a trial summary specs. and is sponsor approved

[pink_poodle]

Here is a workaround e.g., for α :
1. Find/Replace to "alpha" in excel
2. import
3. Replace "alpha" with α in SAS (https://www.lexjansen.com/pharmasug/2010/CC/CC19.pdf) :
%macro specchar(n=, symbol=);
%global &symbol;
data _null_;
char=input(&n., $UCS2B4.);
call symput("&symbol.",trim(left(char)));
run;
%put symbol=&&&symbol..;

data test;
var1 = "alpha";
var2 = tranwrd(var1, "alpha", "&&&symbol..");
run;

%mend;
%specchar(n='0040'x, symbol=alpha); *this is not exactly alpha, but 'at', the format does not show a symbol unless its code starts with '00' on a character map, you would need to explore that further to make this work;

[NehaChopra]

Spoiler

Changing the excel sheet is not a solution in my case as we cannot touch the excel sheet. this might lead in Pinnacle Issue.



Thanks 

Neha 

[Tom]

If you want to read an XLSX file with non-ASCII characters in it then you probably need to be running SAS with encoding option set to UTF-8.   If you are using a single byte encoding like WLATIN1 then there are only 256 characters that can be represented.

The encoding option can only be set when SAS starts.  Look for an option that use UTF-8.  Perhaps it is called unicode support. 

How are you running SAS?  Are you running a sas command from the command line? Are you using SAS Display Manager interactive to run SAS?  Or are you using some front-end interface to generate and run your SAS code.  Like SAS/Studio or Enterprise Guide?