topic Re: Importing special characters from Excel to sas in SAS Programming

Importing special characters from Excel to sas

NehaChopra — Tue, 22 Feb 2022 10:29:11 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 from Excel to sas

SASJedi — Tue, 22 Feb 2022 13:56:03 GMT

What I THINK might be going on here is that you are using an Excel spreadsheet containing Unicode characters and are trying to read that spreadsheet into ASCII (wlatin1) encoded SAS session. You can tell the SAS session encoding by submitting this code and looking in your log:

%put &=sysencoding;

If the log says:

SYSENCODING=wlatin1

then I'm pretty sure that's the issue. If it says this instead:

SYSENCODING=utf-8

then try reading the Excel file using the LIBNAME engine and a DATA step like this:

libname xl "C:\STATISTICS\ Trial Design_Specs.xlsx"; 
data want;
   set xl.ts;
run;
libname xl clear;

and see if that helps...

Re: Importing special characters from Excel to sas

NehaChopra — Tue, 22 Feb 2022 14:22:23 GMT

Thanks , I will try and let you know if this works 🙂