output CSV file containing numbers with commas as string

[buddha_d]

Community, 

           I am just trying to figure out how to write a string to .csv file. Here is my attempt.

data one;
input (acct_id gender)($) charge $20.;
datalines;
101 M 20000,10000,20000
102 F 20000,21000,11000
103 F 50000,18000,49000
;
run;

data _null;
	set one;
	file 'C:\statements\expenses.csv';
	if _n_=1 then do;
	put "Acct_id,Gender,Expenses";
	end;
	put Acct_id ',' gender ',' charge ;
run;

The output in csv file I want it to look like exactly how the input data looked like. The commas are breaking in each cell (for example the first row with charge should look like 20000,10000,20000 in a single excel cell instead of showing up as 20000 10000 20000 in 3 different cells). I can't think of a way to do it. Any suggestions please 

Thanks,

[Tom · Accepted solution]

You don't have to add the commas, just tell the data step you want to write a delimited file by adding the DSD option to the FILE statement.  Then it will automatically add the quotes for any values that have commas in them.

58    data _null;
59      set one;
60     * file 'C:\statements\expenses.csv' dsd ;
61      file log dsd;
62      if _n_=1 then put "Acct_id,Gender,Expenses";
63      put Acct_id  gender charge ;
64    run;

Acct_id,Gender,Expenses
101,M,"20000,10000,20000"
102,F,"20000,21000,11000"
103,F,"50000,18000,49000"

[buddha_d]

Thanks Tom for quick reply.

It works perfectly fine for Log window. But, with excel it shows differently. Any other way to work around with excel output? 

Thanks again

[Tom]

---

@buddha_d wrote:

Thanks Tom for quick reply.

It works perfectly fine for Log window. But, with excel it shows differently. Any other way to work around with excel output? 

Thanks again

---

If you remove the FILE LOG statement and uncomment the other FILE statement it will write a CSV file.

Note that a CSV is just a text file.  How does EXCEL come into the question?  Are you opening the CSV file with Excel? HOW are you opening the CSV file with Excel. Also make sure you don't still have the file open in Excel when you run the data step or you will probably get an error that SAS could not open the file for writing.

Personally I never let Excel open a CSV file by itself because it will in general mangle the data.  Instead open Excel and then ask it to import the CSV file and it will give you options to control how it converts the text into cell values.

[buddha_d]

Thank Tom for the clarification. My computer defaults .csv files to excel, but .txt file seems alright. 

Thanks for your help..