BookmarkSubscribeRSS Feed
chi0202
Calcite | Level 5


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!

9 REPLIES 9
Kfhansen
Calcite | Level 5

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

art297
Opal | Level 21

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

chi0202
Calcite | Level 5

Hi Arthur,

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

Thanks!

art297
Opal | Level 21

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

chi0202
Calcite | Level 5

Hi Arthur,

I'm running SAS 9.3 on Windows Server 2008.

PS. using dbms=excel

art297
Opal | Level 21

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.

nico_
Calcite | Level 5

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

Tom
Super User Tom
Super User

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;

GraphGuy
Meteorite | Level 14

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 8491 views
  • 4 likes
  • 6 in conversation