04-15-2014 10:47 AM
Hi SAS experts,
I want to export a sas dataset into excel and it looks something like this:
NAME ID COMMENTS TIMESTAMP
Heather 123 Heather likes oranges. 08MAY2013:00:00:00.0000
Doesn't like apples. <<notice the new line
Jonathan 456 John likes grapes. Doesn't like pears 08MAY2013:00:00:00.0000
proc export data=comments outfile="...."
But because of the new line, 'Doesn't like apples' became another NAME. I tried finding ways to read past a new line but most methods could only be applied to importing a dataset.
Is there a way to read past the new line for proc export?
Thanks in advance!
04-15-2014 11:06 AM
You say CSV but then Excel, those are two different formats.
CSV is a comma delimited file so your output would be as below. CSV would not have a way to differentiate lines.
Heather, 123, Heather likes oranges. Doesn't Like apples. , 08MAY2013:00:00:00.0000
Are you trying to create an excel file or a CSV file?
In that excel file for the second row, are you expecting a second set of cells or the cell for comments to have 2 lines?
These are different solutions with different approaches...the first is much easier.
04-15-2014 11:23 AM
For a cell in an export xls file to have a return you would need to embed the character for it. In the same way as you would embed RTF code directly into text to get layout functionality. I doubt also that proc export would be sufficient for that task. I would suggest moving to ods tagsets.excelxp. I couldn't see the split char at a quick glance and am leaving now.
04-15-2014 07:13 PM
I'm willing to bet your original data has all sorts of tabs and line feed or carriage return/line feed characters (user pressed the Enter key depending on OS).
You'll need to clean your data and replace them with a space
BTW, exporting to CSV you likely need to check your data for folks entering mismatched single or double quotes as well.
I have cleaned literally thousands of comments entered by folks and these are very common issues.