While exporting to csv file by using proc export, values of one (large) column are shifting to other

[DrAbhijeetSafai]

Dear All,

Greetings!

I have a dataset of in which one large variable is recently added. This newly added variable was not getting read properly so I used termstr=crlf and that csv file got read properly. It had values in excel at multiple levels (one can enter them by using alt+enter) and only first line was getting read. I used termstr=crlf  and the issue got solved. Now I am able to read and process those values (maximum length is somewhat 1000 characters) in that column properly.

When I am trying to write those values after processing to an external csv file, that is causing a problem. The problem is that where there are many lines in that row, only first line is getting printed in csv properly at that column and remaining values are coming to the first column and all other values are blank for those rows. Meaning only large values are shifted from their position to the first column in the csv.

I would like to know if there is any option like termstr=crlf  for exporting a file? I mean if termstr=crlf  can be used for importing, can similar option be used for exporting? Because the problem is similar in nature. While importing I was not able to read the values on lines below the first line (which has got solved by using termstr=crlf ) and now the issue is that I am not able to write values in such a way that all the lines are in the same row (and column). 

In the dataset, the values are perfect. The issue is with proc export in my opinion. 

Thanks in advance!

P.S. - I have attached a file of some sample output. Please look at the first column of order number. Values of a column have come to this first column instead or order number. 

- Dr. Abhijeet Safai

[Reeza]

How are you checking the CSV file/columns? In Excel or in a text editor?

Have you tried opening Excel, importing the file via the import data and specifying the delimiters as CSV? Sometimes that works better than how it automatically reads a file. 

---

@DrAbhijeetSafai wrote:

Dear All,

Greetings!

I have a dataset of in which one large variable is recently added. This newly added variable was not getting read properly so I used termstr=crlf and that csv file got read properly. It had values in excel at multiple levels (one can enter them by using alt+enter) and only first line was getting read. I used termstr=crlf  and the issue got solved. Now I am able to read and process those values (maximum length is somewhat 1000 characters) in that column properly.

When I am trying to write those values after processing to an external csv file, that is causing a problem. The problem is that where there are many lines in that row, only first line is getting printed in csv properly at that column and remaining values are coming to the first column and all other values are blank for those rows. Meaning only large values are shifted from their position to the first column in the csv.

I would like to know if there is any option like termstr=crlf  for exporting a file? I mean if termstr=crlf  can be used for importing, can similar option be used for exporting? Because the problem is similar in nature. While importing I was not able to read the values on lines below the first line (which has got solved by using termstr=crlf ) and now the issue is that I am not able to write values in such a way that all the lines are in the same row (and column). 

In the dataset, the values are perfect. The issue is with proc export in my opinion. 

Thanks in advance!

- Dr. Abhijeet Safai

---

[DrAbhijeetSafai]

@Reeza , I am checking them in excel file. I have just attached the sample of the output. 

I did not understand what you mean to say by importing the file for this issue. The issue for import is solved and this is issue with export. Kindly let me know if any other information is required for this. 

Many thanks for your response.

- Dr. Abhijeet Safai

[Reeza]

Excel is automatically using both of those as delimiters when reading the file, assuming you're just double clicking the file. 

Another way to get around this should also be the method I've suggested. 

To Import a CSV rather than Open it in Excel see the link below for instructions. 

https://www.fcc.gov/general/opening-csv-file-excel

• Open Excel
• Click New, then click on Blank workbook
• Click on the Data tab
• Click Get External Data From Text
• Navigate to the CSV-formatted file saved on your system, select it, then click Import (When browsing to find the file, set browser to look for All Files or for Text Files)
• This will open the Text Import Wizard
• In Import Step 1 of 3 – Click on Delimited, then click Next
• In Import Step 2 of 3 – Check the Comma box, then click Next
• In Import Step 3 of 3 – Select Text and click Finish, then OK

---

@DrAbhijeetSafai wrote:

@Reeza , I am checking them in excel file. I have just attached the sample of the output. 

I did not understand what you mean to say by importing the file for this issue. The issue for import is solved and this is issue with export. Kindly let me know if any other information is required for this. 

Many thanks for your response.

- Dr. Abhijeet Safai

---

[DrAbhijeetSafai]

Thank you @Reeza !

- Dr. Abhijeet Safai

[ballardw · Accepted solution]

If Termstr=CRLF "fixed" the read problem it is highly likely that there are linefeed characters that you just don't see. Viewing the data set with the table viewer and/or proc print output in the results window will not show this.

I suggest that you try this code to see the values that contain line feeds.

data examine;
   set <your data set name goes here> (keep=<name of the problem variable>);
   where index(<name of problem variable>,'0A'x)>0;
run;

Fix is simple

data new;
   set have;
   var=compress(var,'0A'x);
run;

[DrAbhijeetSafai]

That was just fantastic @ballardw ! It solved the issue! I am so happy to get to know the answer even though I was not able to articulate the problem well in my opinion. But when someone can articulate the problem well, they can solve it as well on their own. So inability to articulate it and inability to solve it are the same things in that sense. 

The skill of the answerer is to identify it even though it is not well articulated! I am so glad that you possess that great ability. I am grateful to you.

Thank you very much!

- Dr. Abhijeet Safai