proc export csv. csv shows one record in multiple lines

[jojozheng]

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!

[ballardw]

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).

[jojozheng]

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?

[ballardw]

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.

[jojozheng]

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

[ballardw]

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.

[Reeza]

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!

---