stpsrv_header used to export file, but it has double quotes around text

macroCharlie — Tue, 14 Aug 2012 17:45:56 GMT

I have a small stored process to export a file back to the user after an earlier sp is run:

It works great sending back a dialog prompt to Open or Save the File.

If you select Open, it opens in Excel and you can save as csv without the double quotes.

If you select Save, it saves the file with double quotes around a char data.

Does anyone, perhaps Cynthia, know how to code this so when you Save the file it doesn't have the double quotes?

Re: stpsrv_header used to export file, but it has double quotes around text

Quentin — Tue, 14 Aug 2012 20:08:59 GMT

Hmmm,

Sounds like tagsets.csv is writing the quotes around text values. I don't see an option for controlling that behavior.

I suppose you could try modifying the tagset (or creating your own).

Or may be easier to use a data _null_ step to write the csv file .

HTH,

--Q.

Re: stpsrv_header used to export file, but it has double quotes around text

Cynthia_sas — Tue, 14 Aug 2012 22:15:27 GMT

Hi,

When I ran the code below, using SAS 9.3 (not in a stored process), even with my numbers as character variables in SAS, my CHARAGE, CHARHT and CHARWT variables were not quoted (see TESTDUBL1.PNG screenshot). But, when I flipped the suboption to QUOTE_BY_TYPE='Yes', then I forced quotes around CHARAGE, CHARHT and CHARWT (see TESTDUBL2.PNG screenshot). Finally, using the QUOTED_COLUMNS suboption, I turned on quoting for CHARWT, but essentially turned it off for CHARAGE and CHARHT.

I do not currently have a machine with a Platform install image to test any code as a stored process. And on my computer, I only have SAS 9.3, local install. If you are running SAS 9.2, I'm not sure what suboptions were in effect with ODS CSV or what their defaults were set to. Tech Support could run your stored process on either 9.2 or 9.3 and help you figure things out.

I do have some other comments, though:

--With ODS CSV, character data is ALWAYS quoted. So, for example, even using the QUOTED_COLUMNS suboption, I can only impact whether numbers stored as character are quoted. I could not turn off the quoting around NAME or SEX variables (in my data).

--What does it matter whether the CSV file is saved with double quotes or not? When I open a CSV file with Excel, if the file has double quotes around a number, I don't see the double quotes inside Excel (see TESTDUBL_ALL_QUOTES_IN_EXCEL.PNG screenshot) Are you using the CSV file for some other application?

--I'm not sure exactly what you mean when you say that you are "exporting" a file back to a user -- ODS is not "technically" an export -- you are just sending an ASCII text file that Excel knows how to open and render. The fact that you are using STPSRV_HEADER implies to me that you are doing everything on the web, possibly through the Portal or the Stored Process Web App. Why not use the Add-in for Microsoft Office? Just curious?

--There is an alternate method to generate output from a stored process WITHOUT using %STPBEGIN and %STPEND...it is quite handy when you have extensive suboption lists. So, instead of what you show with the %LETfor _ODSDEST and the %STPBEGIN/%STPEND, you would still have your DATA step for STPSRV_HEADER, etc. and then you would have:

DATA _NULL_;

...same code...;

RUN;

ODS CSV FILE=_WEBOUT options(quoted_columns='135');

...code...

ODS CSV CLOSE;

So, the bottom line is that I wonder what version of SAS you are running on the server, and if 9.2 you probably should work with Tech Support on this question. If you are using 9.3 on the server, then I'd recommend that you investigate using QUOTED_COLUMNS -- however, that recommendation comes with a warning that true character variables, like NAME will ALWAYS be quoted. If you have AGE (or some number) stored as a character variable, then you can turn off the quoting.

cynthia

data allchar(keep=name sex charage charht charwt);

length charage $2 charht charwt $6;

set sashelp.class;

charage = put(age,2.0);

charht = put(height,6.2);

charwt = put(weight,6.2);

if _n_ le 3;

run;

** By default, my numeric columns are NOT quoted;

** Using SAS 9.3;

ods csv file='c:\temp\testdubl1.csv'

options(doc='Help');

proc print data=allchar noobs;

var name sex charage charht charwt;

run;

ods csv close;

** this should quote EVERY var, because they are all character;

ods csv file='c:\temp\testdubl2.csv'

options(quote_by_type='yes');

proc print data=allchar noobs;

var name sex charage charht charwt;

run;

ods csv close;

** this should NOT quote CHARAGE and CHARHT;

ods csv file='c:\temp\testdubl3.csv'

options(quoted_columns='125');

proc print data=allchar noobs;

var name sex charage charht charwt;

run;

ods csv close;

** this will still quote NAME and SEX variable values;

ods csv file='c:\temp\testdubl4.csv'

options(quoted_columns='5');

proc print data=allchar noobs;

var name sex charage charht charwt;

run;

ods csv close;

Re: stpsrv_header used to export file, but it has double quotes around text

macroCharlie — Wed, 15 Aug 2012 11:40:47 GMT

OK, here goes.

The Stored Process Server is SAS 9.3.

The SP is run from a web based application and there is no MSOFFICE.

Thanks to your comment about quoted_columns I was able to solve the problem.

I did a search on SAS quoted_columns and came up with another answer on a forum about ODS CSV w/o quotes.

Richard A. DeVenezia suggested modifying the CSV Tagset and that is what I did.

Richard's code attached.

Charlie

Re: stpsrv_header used to export file, but it has double quotes around text

Quentin — Wed, 15 Aug 2012 14:06:31 GMT

Glad that modifying the tagset worked. Thank's for posting the code. SAS-L is a great resource.

topic Re: stpsrv_header used to export file, but it has double quotes around text in Developers

stpsrv_header used to export file, but it has double quotes around text

Re: stpsrv_header used to export file, but it has double quotes around text

Re: stpsrv_header used to export file, but it has double quotes around text

Re: stpsrv_header used to export file, but it has double quotes around text

Re: stpsrv_header used to export file, but it has double quotes around text