topic Re: ODS CSV output... unable to print a few variable with quotes in ODS and Base Reporting

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

Saurabh_1 — Fri, 13 May 2016 13:23:34 GMT

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:

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

RW9 — Fri, 13 May 2016 13:52:42 GMT

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;

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

Saurabh_1 — Fri, 13 May 2016 14:45:18 GMT

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("," ,

---

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.

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

RW9 — Fri, 13 May 2016 16:05:19 GMT

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;

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

FreelanceReinh — Fri, 13 May 2016 16:16:43 GMT

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.

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

Ksharp — Sat, 14 May 2016 06:44:48 GMT

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;

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

Tom — Sat, 14 May 2016 12:57:59 GMT

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"