Importing excel files with line breaks

Reply
New Contributor
Posts: 3

Importing excel files with line breaks


Hi,

I am trying to import an excel file into SAS using the proc import command.

However, some texts in the excel file are truncated when imported into SAS (because some cells contain text that are very long).

I tried to convert the excel file into .csv before importing into SAS, but because there are line breaks in the cells, each separate line is imported as a different variable in SAS.

ex. the excel file looks like this:

CaseComments
23

Positive IgG test.

Negative IgM test.

Patient hospitalized on Nov.12.

Any solution to this?

Thanks!

Occasional Contributor
Posts: 15

Re: Importing excel files with line breaks

I would remove all the line breaks from your sells before exporting.

You can do that by a doing a replace (ctrl+h). hold ALT while entering 010 in the "find what" field and enter a space in "replace with"

-Karsten

Esteemed Advisor
Posts: 7,294

Re: Importing excel files with line breaks

: Of course you can use SAS to accomplish the same kind of edit of a CSV file and not have to do any of the edits in Excel. e.g.:

filename datain "c:\art\multlines.csv";

filename dataout temp;

data _null_;

  file dataout;

  infile datain LRECL=1 RECFM=F;

  input;

  if _infile_ NE "0A"x then do;

    if _infile_ eq "0D"x then put ;

    else put _infile_@;

  end;

run;

proc import datafile=dataout

  out=test dbms=csv replace;

run;

New Contributor
Posts: 3

Re: Importing excel files with line breaks

Hi Arthur,

Tried running your code, but the content in one cell still gets split into different variables in SAS.

Thanks!

Esteemed Advisor
Posts: 7,294

Re: Importing excel files with line breaks

Interesting!  Worked perfectly for me.  What version of SAS are you running and on which operating system?

New Contributor
Posts: 3

Re: Importing excel files with line breaks

Hi Arthur,

I'm running SAS 9.3 on Windows Server 2008.

PS. using dbms=excel

Esteemed Advisor
Posts: 7,294

Re: Importing excel files with line breaks

Hopefully, when you tried the suggested code, you changed the dbms= to be dbms=csv and included a large enough guessing rows setting to capture the longest string.

Otherwise, I think you will either have to post a copy of the workbook, and the full code that you are using, in order for any of the forum members to be of help.  Of course, you also have the option of contact SAS tech support.

Senior User
Posts: 1

Re: Importing excel files with line breaks

Arthur, thank you so much! This finally solved my problem after an eternal Google search.

Super User
Super User
Posts: 6,343

Re: Importing excel files with line breaks

Typically you can prevent SAS from treating those line breaks as end-of-line by using the TERMSTR=CRLF on the INFILE statement.   Whether PROC IMPORT is smart enough to honor the setting I do not know.

Try reading the file using a data step to see if it handles the line breaks.

This code will read the first two rows and show you the value of each cell as a character string.

data _null_;

  infile '../raw/_domains.csv' dsd truncover termstr=crlf col=cc length=len obs=3 lrecl=1000000 ;

  input @;

  row+1;

  do col=1 by 1 until (cc > len) ;

    length cell $5000 ;

    input cell @;

    put row= col= cell= ;

  end;

run;

SAS Employee
Posts: 967

Re: Importing excel files with line breaks

In addition to Arthur's question (especially, are you running on Windows or Unix), also are you using dbms=excel or dbms=xls?

Could you include the whole 'proc import' in your reply?

Ask a Question
Discussion stats
  • 9 replies
  • 3233 views
  • 4 likes
  • 6 in conversation