topic Importing Special Characters in SAS in Statistical Procedures

Importing Special Characters in SAS

NehaChopra — Mon, 21 Feb 2022 16:44:27 GMT

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

Re: Importing Special Characters in SAS

pink_poodle — Mon, 21 Feb 2022 22:04:06 GMT

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

Re: Importing Special Characters in SAS

pink_poodle — Mon, 21 Feb 2022 22:07:07 GMT

Here is another way:
https://communities.sas.com/t5/SAS-Programming/Import-data-from-MS-access-Excel-while-retaining-special/m-p/352237#M82077

Re: Importing Special Characters in SAS

NehaChopra — Tue, 22 Feb 2022 10:30:43 GMT

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

Re: Importing Special Characters in SAS

NehaChopra — Tue, 22 Feb 2022 10:31:28 GMT

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

Re: Importing Special Characters in SAS

SteveDenham — Tue, 22 Feb 2022 13:09:14 GMT

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

Re: Importing Special Characters in SAS

NehaChopra — Tue, 22 Feb 2022 14:21:01 GMT

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

Re: Importing Special Characters in SAS

Tom — Tue, 22 Feb 2022 15:52:05 GMT

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?

Re: Importing Special Characters in SAS

pink_poodle — Tue, 22 Feb 2022 16:07:09 GMT

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;

Re: Importing Special Characters in SAS

NehaChopra — Fri, 25 Feb 2022 16:09:24 GMT

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