DATA Step, Macro, Functions and more

Import string with double dash from Excel

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Import string with double dash from Excel

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


Accepted Solutions
Solution
‎02-27-2012 04:01 PM
Regular Contributor
Posts: 233

Import string with double dash from Excel

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:

ObsHosp1Hosp2
1hospital name -- sample 1hospital name -- sample 2

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

View solution in original post


All Replies
PROC Star
Posts: 7,492

Import string with double dash from Excel

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

Occasional Contributor
Posts: 7

Import string with double dash from Excel

My SAS codes:

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

run;

Samle data in Excel is like:

Hosp1Hosp2
hospital name – sample 1hospital name – sample 2


The data after read in SAS:

hospital name !! sample 1

hospital name !! sample 2

Thanks!

Regular Contributor
Posts: 233

Re: Import string with double dash from Excel

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

New Contributor
Posts: 3

Import string with double dash from Excel

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

Regular Contributor
Posts: 233

Import string with double dash from Excel

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

New Contributor
Posts: 3

Import string with double dash from Excel

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

PROC Star
Posts: 7,492

Import string with double dash from Excel

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.

Occasional Contributor
Posts: 7

Import string with double dash from Excel

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

PROC Star
Posts: 7,492

Import string with double dash from Excel

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

Occasional Contributor
Posts: 7

Import string with double dash from Excel

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!

PROC Star
Posts: 7,492

Re: Import string with double dash from Excel

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

Solution
‎02-27-2012 04:01 PM
Regular Contributor
Posts: 233

Import string with double dash from Excel

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:

ObsHosp1Hosp2
1hospital name -- sample 1hospital name -- sample 2

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 430 views
  • 0 likes
  • 4 in conversation