topic Re: Importing excel files with line breaks in SAS Programming

Importing excel files with line breaks

chi0202 — Thu, 21 Nov 2013 14:52:06 GMT

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:

Case	Comments
23	Positive IgG test. Negative IgM test. Patient hospitalized on Nov.12.

Any solution to this?

Thanks!

Re: Importing excel files with line breaks

Kfhansen — Thu, 21 Nov 2013 16:27:20 GMT

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

Re: Importing excel files with line breaks

art297 — Thu, 21 Nov 2013 19:22:08 GMT

: 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;

Re: Importing excel files with line breaks

chi0202 — Fri, 22 Nov 2013 18:31:26 GMT

Hi Arthur,

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

Thanks!

Re: Importing excel files with line breaks

Tom — Fri, 22 Nov 2013 18:45:48 GMT

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;

Re: Importing excel files with line breaks

art297 — Sun, 24 Nov 2013 04:46:41 GMT

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

Re: Importing excel files with line breaks

GraphGuy — Wed, 27 Nov 2013 20:55:58 GMT

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?

Re: Importing excel files with line breaks

chi0202 — Thu, 28 Nov 2013 15:28:33 GMT

Hi Arthur,

I'm running SAS 9.3 on Windows Server 2008.

PS. using dbms=excel

Re: Importing excel files with line breaks

art297 — Thu, 28 Nov 2013 15:45:39 GMT

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.

Re: Importing excel files with line breaks

nico_ — Fri, 05 May 2017 09:19:14 GMT

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