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!
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
: 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;
Hi Arthur,
Tried running your code, but the content in one cell still gets split into different variables in SAS.
Thanks!
Interesting! Worked perfectly for me. What version of SAS are you running and on which operating system?
Hi Arthur,
I'm running SAS 9.3 on Windows Server 2008.
PS. using dbms=excel
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.
Arthur, thank you so much! This finally solved my problem after an eternal Google search.
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;
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?
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.
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.