topic Re: Proc Export CSV Still Seeing Commas as Delimiter in SAS Programming

Proc Export CSV Still Seeing Commas as Delimiter

shu1225 — Tue, 02 Jul 2024 15:05:31 GMT

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?

Re: Proc Export CSV Still Seeing Commas as Delimiter

SASJedi — Tue, 02 Jul 2024 15:33:07 GMT

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?

Re: Proc Export CSV Still Seeing Commas as Delimiter

shu1225 — Tue, 02 Jul 2024 15:36:35 GMT

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.

Re: Proc Export CSV Still Seeing Commas as Delimiter

SASJedi — Tue, 02 Jul 2024 15:48:43 GMT

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?

Re: Proc Export CSV Still Seeing Commas as Delimiter

Reeza — Tue, 02 Jul 2024 15:52:11 GMT

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

Re: Proc Export CSV Still Seeing Commas as Delimiter

shu1225 — Tue, 02 Jul 2024 16:10:42 GMT

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

Re: Proc Export CSV Still Seeing Commas as Delimiter

Tom — Tue, 02 Jul 2024 16:41:04 GMT

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

Re: Proc Export CSV Still Seeing Commas as Delimiter

shu1225 — Tue, 02 Jul 2024 16:43:00 GMT

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!