topic Re: proc export problem in SAS Procedures

proc export problem

shixin — Tue, 15 Apr 2014 14:47:48 GMT

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="...."

dbms=csv

replace;

run;

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!

Cheerin

Re: proc export problem

data_null__ — Tue, 15 Apr 2014 15:01:28 GMT

What does the SAS data set look like exactly?

Re: proc export problem

Reeza — Tue, 15 Apr 2014 15:06:54 GMT

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.

Re: proc export problem

RW9 — Tue, 15 Apr 2014 15:23:28 GMT

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.

Re: proc export problem

ballardw — Tue, 15 Apr 2014 23:13:26 GMT

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.