topic Import string with double dash from Excel in SAS Programming

Import string with double dash from Excel

yyangca — Mon, 27 Feb 2012 19:06:18 GMT

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

Import string with double dash from Excel

art297 — Mon, 27 Feb 2012 19:16:19 GMT

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

Re: Import string with double dash from Excel

Hima — Mon, 27 Feb 2012 19:28:56 GMT

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

Import string with double dash from Excel

DMartin — Mon, 27 Feb 2012 19:42:21 GMT

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

Import string with double dash from Excel

Hima — Mon, 27 Feb 2012 19:44:10 GMT

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

Import string with double dash from Excel

art297 — Mon, 27 Feb 2012 19:45:52 GMT

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.

Import string with double dash from Excel

DMartin — Mon, 27 Feb 2012 19:47:05 GMT

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

Re: Import string with double dash from Excel

art297 — Mon, 27 Feb 2012 20:02:49 GMT

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,);

Import string with double dash from Excel

yyangca — Mon, 27 Feb 2012 20:56:35 GMT

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!

Import string with double dash from Excel

Hima — Mon, 27 Feb 2012 21:01:53 GMT

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?

Import string with double dash from Excel

yyangca — Mon, 27 Feb 2012 21:16:57 GMT

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

Import string with double dash from Excel

art297 — Mon, 27 Feb 2012 21:22:37 GMT

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

Import string with double dash from Excel

yyangca — Mon, 27 Feb 2012 21:57:26 GMT

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!