Import string with double dash from Excel

[yyangca]

Hello,

I try to use proc import to import charactor variables (length $80. ) including double dash (–) from Excel 97-2003 worksheet. I use SAS 9.2 version. The double dash becomes to !! after imported to SAS. Could you please help? Thanks in advance.

Ying

[art297]

Can you post a copy of a worksheet, and your import code, that exhibits that behavior?

[yyangca]

My SAS codes:

proc import datafile="Z:\project1\myfile.xls" out=hosp DBMS=xls replace:

run;

Samle data in Excel is like:

Hosp1	Hosp2
hospital name – sample 1	hospital name – sample 2

The data after read in SAS:

hospital name !! sample 1

hospital name !! sample 2

Thanks!

[Hima]

My excel has the below data

Desc

This is a sample test -- data step test for sas forums

Code:
PROC IMPORT OUT= TEST
            DATAFILE= "C:\Documents and Settings\TEST.xls"
            DBMS=EXCEL REPLACE;
RUN;

PROC PRINT; RUN;

Output:

Obs    Desc

  1      This is a sample test -- data step test for sas forums

Log:

33   PROC IMPORT OUT= TEST
34               DATAFILE= "C:\Documents and Settings\TEST.xls"
35               DBMS=EXCEL REPLACE;
36   RUN;

NOTE: WORK.TEST data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           2.56 seconds
      cpu time            0.60 seconds

37
38   PROC PRINT; RUN;

NOTE: There were 1 observations read from the data set WORK.TEST.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

[DMartin]

So what is the problem? Your output matches your input.

[Hima]

Question was posed by yyangca . I replied him with an answer. That's not me who posed the question .

[DMartin]

Oops!! Sorry about that. Must remember to engage brain before putting fingers in gear. :smileyblush:

[art297]

That wasn't the OP who posted the above test.  While the OP hasn't posted an example that shows the problem, my initial guess is that the "double dash" is actually an emdash character, and that the result of the import isn't !! but, rather, a box appearing character.

[yyangca]

Awesome. It works!! Thank you so much for your help.

[art297]

yyangca: what worked?  And, if the problem is solved, you should mark the answer as being correct.

[yyangca]

Replace DBMS=xls with DBMS=EXCEL in my original proc import. That way data are read in with "–" displayed correctly. I just marked the anwser from HIMA as correct answer. Thank you!

[art297]

To test whether it is an emdash, can you change it to a dash with the following command in a datastep?:

variable=translate(variable,"-","14"x,);

[Hima · Accepted solution]

I re-ran the code with your data.

PROC IMPORT OUT= TEST
            DATAFILE= "C:\Documents and Settings\TEST.xls"
            DBMS=EXCEL REPLACE;
RUN;

PROC PRINT; RUN;

Output:

Obs	Hosp1	Hosp2
1	hospital name -- sample 1	hospital name -- sample 2

Can you re-run by replacing xls to EXCEL in your code?