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:
@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.
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?
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.
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?
I am verifying the contents of the CSV file in Excel.
@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.
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!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: