BookmarkSubscribeRSS Feed
macroCharlie
Calcite | Level 5

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

     sas code.jpg

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?

4 REPLIES 4
Quentin
Super User

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.


The Boston Area SAS Users Group is hosting free webinars!
Next up: Joe Madden & Joseph Henry present Putting Power into the Hands of the Programmer with SAS Viya Workbench on Wednesday Nov 6.
Register now at https://www.basug.org/events.
Cynthia_sas
SAS Super FREQ

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;


testdubl1.pngtestdubl3.pngtestdubl2_all_quotes_in_Excel.pngtestdubl2.png
macroCharlie
Calcite | Level 5

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

Quentin
Super User

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

The Boston Area SAS Users Group is hosting free webinars!
Next up: Joe Madden & Joseph Henry present Putting Power into the Hands of the Programmer with SAS Viya Workbench on Wednesday Nov 6.
Register now at https://www.basug.org/events.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 2499 views
  • 0 likes
  • 3 in conversation