Proc Export CSV Still Seeing Commas as Delimiter

[shu1225]

Hi,

I am trying to export a very large dataset from SAS to CSV using "|" as my delimiter. I'm using the code as follows:

filename output "filepath" encoding="UTF-8";

proc export data = dataset

outfile = output

dbms = csv replace;

delimiter="|";

run;

However, some of the fields in the dataset contain commas and they are still being read as delimiters in the CSV files, causing columns to be broken up. Is there another way the delimiter needs to be coded to make sure commas are not read as delimiters?

[SASJedi]

I'm not understanding the issue. Here is sample code:

data test;
	infile datalines dsd dlm='|';
	input Text1:$30. Text2:$30. Num1:32.; 
datalines;
Now is the time|for all good men|1
Now, it's the time|for all, good men|2
A,B,C,D|E,F,G,H|3
;

filename output "test.dlm" encoding="UTF-8";

proc export data = test
	outfile = output
	dbms = csv replace;
	delimiter="|";
run;

Which produces this result:

Text1|Text2|Num1
Now is the time|for all good men|1
Now, it's the time|for all, good men|2
A,B,C,D|E,F,G,H|3

Which is what I would expect. What were you expecting instead?

[shu1225]

That is also what I would have expected. However, what I'm getting is this:

Text1|Text2|Num1
Now is the time|for all good men|1
Now| it's the time|for all| good men|2
A|B|C|D|E|F|G|H|3

All commas are still being read as delimiters.

[SASJedi]

I just ran this and got the results I showed you. Can you please run the test program above in a fresh SAS session and then share the complete log? 

[Reeza]

You say 'being read' are you reimporting the file somehow? How are you verifying the file contents?

[shu1225]

I am verifying the contents of the CSV file in Excel.

[Tom · Accepted solution]

---

@shu1225 wrote:

I am verifying the contents of the CSV file in Excel.

---

Do not use Excel to check the contents of a TEXT file.  Use a TEXT Editor instead, like Notepad.

How did you tell Excel to open the file?  If you let Excel open a file with a .CSV extension by default it will assume the delimiter is a comma.  Instead first open Excel and then use the file import tool to read the file.  They keep moving that around in the menu's but in my current version of Excel it is under Data -> Get Data -> From File -> From Text/CSV

Which should then let you pick a file and pop-up with a screen where YOU can TELL Excel what delimiter to use.

[shu1225]

I did not have Notepad on the laptop I was using. I had input it the way you mentioned for Excel but forgot to check that I needed a custom delimiter. Thanks for the help!