topic Re: proc export csv. csv shows one record in multiple lines in SAS Programming

proc export csv. csv shows one record in multiple lines

jojozheng — Fri, 17 Aug 2018 14:43:03 GMT

Hi everyone,

I use code to get csv:

proc export
data=hh_decrease
dbms=csv
outfile="/teamworkspace/Marketing Research/decrease15_18.csv"
replace;
run;

But output shows variables starting from balsum to the end (blue part) in the second line(red part). But I want them in same line, which is all red values are in the blue part. How can I do it?

Thanks!

Re: proc export csv. csv shows one record in multiple lines

ballardw — Fri, 17 Aug 2018 15:17:07 GMT

First hint: don't look at CSV files in a spreadsheet to see if the structure is correct. The spreadsheet will "interpret" things. Saving the file after making a change like column widths can change values.

I might try printing 10 or 15 observations from proc print to see if the data looks correct before exporting. You might have something in you process that has the expected values in different records with the wrong variable.

If not reexport the data and examine the result in a plain text editor (the SAS editor will work).

Re: proc export csv. csv shows one record in multiple lines

jojozheng — Fri, 17 Aug 2018 15:30:59 GMT

Thank you @ballardw

proc print shows exactly what I want. I checked proc export logs, it seems like records read in right format.... I don't know what I can do next? Can you give me more details?

Re: proc export csv. csv shows one record in multiple lines

ballardw — Fri, 17 Aug 2018 15:37:14 GMT

Did you try re-exporting the data and examining the file in a text editor?

Note that you may have to turn off any line wrap if using notepad or some other similar.

Re: proc export csv. csv shows one record in multiple lines

jojozheng — Fri, 17 Aug 2018 15:50:54 GMT

Yes. I re-exporting and open notepad, there is no line wrap, but there is a word wrap, and I turn off:

Re: proc export csv. csv shows one record in multiple lines

ballardw — Fri, 17 Aug 2018 16:59:22 GMT

Do you still see the same issue with the values appearing on the wrong line?

From what you showed previously I would expect to see that balsum variable value appearing as the second value on a row following a comma. Something like

,218818,,,,31575, (other stuff goes here)

Since you picture didn't include a second line it is not possible to tell. If you do not see something like this than I expect the export worked fine. If you open it in a spreadsheet program and the "second" line appears that wasn't in the csv then it is pretty hard to tell what that program sees to insert a line but it may be an odd value in the data of some form.

Re: proc export csv. csv shows one record in multiple lines

Reeza — Fri, 17 Aug 2018 19:37:32 GMT

That's likely a data quality issue where one of your variables has a line feed in it for some reason.

It will likely be the variable that's the last before the break...you can check it in a text editor and you check for end of line character. You can remove it with COMPRESS - check the options for invisible characters.

@jojozheng wrote:

Hi everyone,

I use code to get csv:

proc export
data=hh_decrease
dbms=csv
outfile="/teamworkspace/Marketing Research/decrease15_18.csv"
replace;
run;

But output shows variables starting from balsum to the end (blue part) in the second line(red part). But I want them in same line, which is all red values are in the blue part. How can I do it?

Thanks!