Hi, I want to create a CSV File with the variable names and values in quotes like
"Variable_1","Variable_2","Variable_3"
"Value_11","Value_21","Value_31"
"Value_12","Value_22","Value_32"
....
...
etc
I'm unable to get the values for three variables in quotes while the rest six are coming in quotes.
can someone suggest what could be the reason for this.
I'm using SAS 9.3 and the code i'm using is :
ODS CSV File = 'H:\Reports\test.csv' noobs;
proc print data = data3;
run;
ODS CSV CLOSE:
What ODS CSV produces is not a real CSV file. It adds too many quotes and it also adds an extra line at the end. But it sounds like what you want is not a real CSV file either. Fortunately it is easy with SAS to produce both a real CSV file and what you want with just a couple of simple steps.
First let's get some test data to use. For example the first three records in SASHELP.CLASS.
data test; set sashelp.class(obs=3); run;
Now let's set the output location. I will use a temp file for this demonstration.
filename csv temp;
The first line of the CSV file is usually the variable names (column headers). You can generate that easily using a couple of steps.
proc transpose data=test(obs=0) ;
var _all_;
run;
data _null_;
file csv dsd ;
set &syslast end=eof;
put _name_ @;
if eof then put ;
run;
Now to write the data lines we just add one more data step.
data _null_;
file csv dsd mod;
set test ;
put (_all_) (+0);
run;
To check if the result is in the proper format let's run a simple data step to show the lines from the output file back into the SAS log.
data _null_;
infile csv;
input;
put _infile_;
run;
So for our little three observation test case the result is the well formed CSV file below. Note that there are no unneeded quote characters or extra blank lines.
Name,Sex,Age,Height,Weight
Alfred,M,14,69,112.5
Alice,F,13,56.5,84
Barbara,F,13,65.3,98
To add all of those extra quote characters that you requested you just need to add the ~ modifier to the put statements.
So in the step that writes the variable names you would use this PUT statement.
put _name_ ~ @;
And in the step that writes the variable values you would use this PUT statement.
put (_all_) (~) ;
With those changes the resulting file will look like this:
"Name","Sex","Age","Height","Weight"
"Alfred","M","14","69","112.5"
"Alice","F","13","56.5","84"
"Barbara","F","13","65.3","98"
The reason is that standard CSV file format (https://en.wikipedia.org/wiki/Comma-separated_values) is thus:
Line 1 contains each variable name separate by a column [Note: optional]
Line > 1 contains each row of data on per line, with data items separated by commas, with the option of quoting data items where its possible special characters may impact the data
You will note that the first line shows Variable names - these would not be quoted. If you really want this (and why?) then you would need to do it by hand in a datastep output - although be sure to not call the file CSV as it wouldn't be a CSV.
data _null_; set your_data; file "somewhere\your_file.txt"; if _n_=1 then put '"Variable_1","Variable_2","Variable_3"'; put catx(",",quote(variable_1),quote(variable_2),quote(variable_3)); run;
Thank you for your reply RW9. Actually, I'm getting the Variable Names in quotes using my earlier code so the issue is only with values.
Also when i run the code you've provided i'm getting this error:
112 data _null_;
113 set RSF_Data3;
114 file 'H:\reports\test2.txt';
115 if _n_ = 1 then put
115! '"Account_number","Name","date_deceased","balance_amt_dod","City","St","Zip"';
116 put catx("," ,
---
22
76
116! quote(Account_number),quote(Name),quote(date_deceased),quote(balance_amt_dod),quote(City),
116! quote(ST),quote(Zip));
ERROR 22-322: Syntax error, expecting one of the following: a name, arrayname, _ALL_,
_CHARACTER_, _CHAR_, _NUMERIC_.
ERROR 76-322: Syntax error, statement will be ignored.
117 run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
Another thing is that my data has missing values, so i'm not sure if catx would be the right function in this case.
Please suggest.
You probably can't put functions in a put statement, I didn't test teh code. Try the below. I would still advise you not put variable names with quotes around them, its not CSV and I wouldn't accept that datafile.
data _null_; length tmp $2000; set your_data; file "somewhere\your_file.txt"; if _n_=1 then put '"Variable_1","Variable_2","Variable_3"'; tmp=catx(",",quote(variable_1),quote(variable_2),quote(variable_3)); put tmp; run;
Or use the tilde modifier to simplify the PUT statement for the variable values:
data _null_;
set RSF_Data3;
file 'H:\reports\test2.txt' dsd;
if _n_ = 1 then put '"Account_number","Name","date_deceased","balance_amt_dod","City","St","Zip"';
put (_all_)(~);
run;
This assumes that the variable names in the first PUT statement are listed in the same order as they are stored in the dataset. The DSD option of the FILE statement implies specifying comma as the delimiter. With a bit more effort you can even create the list of variable names automatically: see this older post.
use options(doc='help') to get the what kind of option you can used in ODS CSV: ODS CSV File = 'c:\temp\test.csv' options(doc='help'); proc print data = sashelp.class noobs; run; ODS CSV CLOSE; especially these option: quote_by_type='yes' quoted_columns='1234' ODS CSV File = 'c:\temp\test.csv' options( quoted_columns='1234'); proc print data = sashelp.class noobs; run; ODS CSV CLOSE;
What ODS CSV produces is not a real CSV file. It adds too many quotes and it also adds an extra line at the end. But it sounds like what you want is not a real CSV file either. Fortunately it is easy with SAS to produce both a real CSV file and what you want with just a couple of simple steps.
First let's get some test data to use. For example the first three records in SASHELP.CLASS.
data test; set sashelp.class(obs=3); run;
Now let's set the output location. I will use a temp file for this demonstration.
filename csv temp;
The first line of the CSV file is usually the variable names (column headers). You can generate that easily using a couple of steps.
proc transpose data=test(obs=0) ;
var _all_;
run;
data _null_;
file csv dsd ;
set &syslast end=eof;
put _name_ @;
if eof then put ;
run;
Now to write the data lines we just add one more data step.
data _null_;
file csv dsd mod;
set test ;
put (_all_) (+0);
run;
To check if the result is in the proper format let's run a simple data step to show the lines from the output file back into the SAS log.
data _null_;
infile csv;
input;
put _infile_;
run;
So for our little three observation test case the result is the well formed CSV file below. Note that there are no unneeded quote characters or extra blank lines.
Name,Sex,Age,Height,Weight
Alfred,M,14,69,112.5
Alice,F,13,56.5,84
Barbara,F,13,65.3,98
To add all of those extra quote characters that you requested you just need to add the ~ modifier to the put statements.
So in the step that writes the variable names you would use this PUT statement.
put _name_ ~ @;
And in the step that writes the variable values you would use this PUT statement.
put (_all_) (~) ;
With those changes the resulting file will look like this:
"Name","Sex","Age","Height","Weight"
"Alfred","M","14","69","112.5"
"Alice","F","13","56.5","84"
"Barbara","F","13","65.3","98"
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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: