The SAS Output Delivery System and reporting techniques

ODS CSV output... unable to print a few variable with quotes

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

ODS CSV output... unable to print a few variable with quotes

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:


Accepted Solutions
Solution
‎05-16-2016 04:26 AM
Super User
Super User
Posts: 5,971

Re: ODS CSV output... unable to print a few variable with quotes

[ Edited ]

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"

 

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 6,704

Re: ODS CSV output... unable to print a few variable with quotes

[ Edited ]

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;

  

New Contributor
Posts: 2

Re: ODS CSV output... unable to print a few variable with quotes

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.

Esteemed Advisor
Esteemed Advisor
Posts: 6,704

Re: ODS CSV output... unable to print a few variable with quotes

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;
Trusted Advisor
Posts: 1,114

Re: ODS CSV output... unable to print a few variable with quotes

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.

Grand Advisor
Posts: 9,325

Re: ODS CSV output... unable to print a few variable with quotes

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;

Solution
‎05-16-2016 04:26 AM
Super User
Super User
Posts: 5,971

Re: ODS CSV output... unable to print a few variable with quotes

[ Edited ]

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"

 

Post a Question
Discussion Stats
  • 6 replies
  • 514 views
  • 4 likes
  • 5 in conversation