topic Re: Removing last comma in csv file from a proc export in SAS Programming

Removing last comma in csv file from a proc export

kmardinian — Wed, 26 Sep 2018 20:19:26 GMT

Hi, I am trying to export a csv file from a SAS dataset I created, but I am trying to figure out a way to get rid of the last comma that shows up when I view the csv file in notepad. Is there a way of doing this? Any help is greatly appreciated! Thank you!

This is the code I am using to export my dataset.

proc export data=dt
   outfile="&Outpath.&outfile..csv"
   dbms=csv replace;
   delimiter='|';
run;

Re: Removing last comma in csv file from a proc export

Reeza — Wed, 26 Sep 2018 20:28:10 GMT

You've specified the delimiter as a pipe, that isn't a csv file. I would recommend the following changes:

1. Change it to a txt file, since it's a pipe delimited text file

2. Change DMBS to DLM (delimited) rather than CSV

proc export data=dt
   outfile="&Outpath.&outfile..txt"
   dbms=DLM replace;
   delimiter='|';
 run;

@kmardinian wrote:

Hi, I am trying to export a csv file from a SAS dataset I created, but I am trying to figure out a way to get rid of the last comma that shows up when I view the csv file in notepad. Is there a way of doing this? Any help is greatly appreciated! Thank you!

This is the code I am using to export my dataset.

proc export data=dt
   outfile="&Outpath.&outfile..csv"
   dbms=csv replace;
   delimiter='|';
run;

Re: Removing last comma in csv file from a proc export

kmardinian — Wed, 26 Sep 2018 20:32:08 GMT

I see, do you know if that would be the only way to do this. There isn't a possibility of fixing this issue, but also keeping it as a csv file? A csv file is what I wanted the output to be in.

Re: Removing last comma in csv file from a proc export

Kurt_Bremser — Wed, 26 Sep 2018 20:40:56 GMT

csv means comma separated volume, so it does not use pipes as delimiter, but commas.

Anyway, anytime the last column in the SAS dataset is empty, the corresponding line in the output will end with a delimiter, per definition.

Re: Removing last comma in csv file from a proc export

Reeza — Wed, 26 Sep 2018 21:01:34 GMT

You can name it whatever you want, but it's not a CSV file. The reason to not do this is to avoid confusion from end users.

You can leave the extension as CSV and make the other changes (DBMS).

@kmardinian wrote:

I see, do you know if that would be the only way to do this. There isn't a possibility of fixing this issue, but also keeping it as a csv file? A csv file is what I wanted the output to be in.

Re: Removing last comma in csv file from a proc export

ballardw — Wed, 26 Sep 2018 21:16:42 GMT

Change

proc export data=dt

proc export data=dt(drop=lastvar)

replace lastvar with the last variable in the data set. You get the "extra" comma or pipe because you have a variable that is all missing values.

OR much less likely the values of your last variable end with a comma. At which point you need to modify your data set.