11-21-2013 09:52 AM
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:
Positive IgG test.
Negative IgM test.
Patient hospitalized on Nov.12.
Any solution to this?
11-21-2013 11:27 AM
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"
11-21-2013 02:22 PM
filename datain "c:\art\multlines.csv";
filename dataout temp;
infile datain LRECL=1 RECFM=F;
if _infile_ NE "0A"x then do;
if _infile_ eq "0D"x then put ;
else put _infile_@;
proc import datafile=dataout
out=test dbms=csv replace;
11-28-2013 10:45 AM
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.
11-22-2013 01:45 PM
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.
infile '../raw/_domains.csv' dsd truncover termstr=crlf col=cc length=len obs=3 lrecl=1000000 ;
do col=1 by 1 until (cc > len) ;
length cell $5000 ;
input cell @;
put row= col= cell= ;
11-27-2013 03:55 PM
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?