The SAS Output Delivery System and reporting techniques

help on output to csv by using ods tagset template

Reply
New Contributor
Posts: 3

help on output to csv by using ods tagset template

Hi,

I am try to export sas data sets to csv file. I want to output all the value as characters in CSV as CSV may read number with dash as date.

I search online and find one solution is add '=" to the value by using the following code.

I encounter one problem that the following code can not be correctly performed when value in variables have comma inside.

DATA test like the following.

Name  Counter    Comments

Em, H  6-8          Test, test, test

proc template;

define tagset Tagsets.test;

parent=tagsets.csv;

define event data;

put ','/ if !cmp( COLSTART , "1" );

put '=' """" / if cmp( TYPE , "string" );

put VALUE;

put """" / if cmp( TYPE , "string" );

end;

end;

run;

ods markup  file="test1.csv"   tagset=tagsets.test;

proc print data=test label noobs;

run;

ods markup close;

However the problem is while value has comma the code doesn't work that well and misinterpret the start value. For example, name, 'Emily, Wintson' will export as two variables which is not.

I try to replace comma to tab-delimited, "09"x in template, but it even didn't work to export correctly.

I know tagset.excelxp can keep the format nicely, but the file turns out using XML file which may not be supported by every client.

Or Is there any other way to auto output csv since I have multiple datasets.

BYW, I am using SAS9.2

Contributor
Posts: 43

Re: help on output to csv by using ods tagset template

Have you tried just using Proc Export?

proc export data = tester outfile="test1.csv" DBMS=csv ;

run ;

This will put double quotes around the string values that have embedded commas, etc.

Oh, wait... I see what you mean about the value "6-8", When you open in Excel, Excel formats to "8-Jun".  That column is not getting double-quotes, while the other two fields with the commas are.

Super User
Super User
Posts: 7,706

Re: help on output to csv by using ods tagset template

Just create a dataset which looks as you want the output to be, i.e. all character, all processing done, then put it out to a CSV.

Super User
Super User
Posts: 6,842

Re: help on output to csv by using ods tagset template

This is not a SAS or a CSV problem. It is a problem with whatever software you are using to read the CSV file.  Based on your description I would assume you are opening the file in Excel. If you don't let Excel automatically convert the fields for you then you can tell it that the column is character and it will not convert the values to numbers or formulas.

Contributor
Posts: 43

Re: help on output to csv by using ods tagset template

I did notice that when I tried to change the column format AFTER opening the file with Excel, that Excel has "conveniently" converted 6-8 to a date, so changing it to text resulted in a large integer value.

If we assume that the file recipients are not Excel-savvy, then it would be better to control the values going in.

I can't even guess what the significance of the value 6-8 is or how one would use it, but I would use a DATA step to convert it to a more recognizable string value.  One could embed double-quotes into the string, so counter = '"6-8"' ; or change it to 2 separate columns, again depending on how one would use it.

Super User
Super User
Posts: 7,706

Re: help on output to csv by using ods tagset template

Ah, Tom is correct then, your problem (as in most cases) is Excel.  A CSV file is a plain text file with data separated by commas.  Unfortunately Excel uses a CSV parser which reads the data, and best guesses the information.  The simplest solution is to stop using Excel in the first place.  However if you really have to get it into a readable format in Excel, the look at ods tagsets.excelxp - this creates XML which can also be parsed by Excel, however as it is a descriptive file (which CSV is not), it can have specific tags embedded which tell Excel what the format of the data is, i.e. removing the guessing part. 

Super User
Super User
Posts: 6,842

Re: help on output to csv by using ods tagset template

Why not just output both a CSV version for people that want to read the data with tools other than EXCEL and an EXCEL version for those that use EXCEL?

proc export outfile='c:\downloads\class.xlsx' dbms=xlsx data=sashelp.class replace;

run;

proc export outfile='c:\downloads\class.csv' dbms=dlm data=sashelp.class replace;

  delimiter=',';

run;

New Contributor
Posts: 3

Re: help on output to csv by using ods tagset template

Hi Tom,

Thanks for your all comments.

However, my company don't have SAS/ACCESS license in SAS and thus dbms=xls or xlsx Doesn't work.

Furthermore, I do think add '=" value " works. But I don't know how to deal with the case that value contains comma. It has the same situation with DATA _NULL_ .

Super User
Posts: 19,063

Re: help on output to csv by using ods tagset template

Two other options:

Tagsets + VBA code to convert file to native excel file - both solutions are explained here on the forum in multiple threads.

43496 - Convert files created using an ODS destination to native Excel files

An Proc Export that doesn't require SAS/ACCESS

A Poor/Rich SAS Users Proc Export - sasCommunity

Super User
Super User
Posts: 6,842

Re: help on output to csv by using ods tagset template

Here is code I did before I gave up on trying to protect users from Excel.

%let vlist=name age sex height weight ;

%let slist=name sex;

%let infile=class;

%let outfile="class.csv";

data _null_;

  file &outfile dsd ;

  if _n_=1 then put "%sysfunc(tranwrd(&vlist,%str( ),%str(,)))";

*----------------------------------------------------------------------;

* Define array _C_ of all string variables. Truncate to 255 characters. ;

*----------------------------------------------------------------------;

  array _c_ $255 &slist;

  set &infile end=_eof_;

/*----------------------------------------------------------------------

Truth Table for Conversions to protect strings in EXCEL

    Checks on the string value

    --------------------------------------

    +> Space is first character

    |+> First DQUOTE(") location > 1

    ||+> First DQUOTE(") location = 1

    |||+> Contains a comma

    ||||

    |||| ------ ACTIONS ---------------

    |||| Convert DQUOTE(") to SQUOTE(')

_X_ ||||  | Convert to equation ="..."

  | |||| | |         Example String    Converted String

-- ---- - -         ---------------   ----------------

  0 0000 X         0001              ="0001"

  1 0001 X         1,200             ="1,200"

  2 0010 X           "ABC"             'ABC'

  3 0011              "1,200"           "1,200"

  4 0100              A "B" C           A "B" C

  5 0101              A,"B", or C       A,"B", or C

  8 1000 X          ABC              =" ABC"

  9 1001 X          A,C              =" A,C"

12 1100 X X          A or "B"         =" A or 'B'"

13 1101 X X          A,"B", or C      =" A,'B', or C"

_X_ in (3 4 5) require no conversion. Excel will treat them as text.

_X_ in (2 3 4 5) adding equation causes Excel to get confused.

_X_ in (6 7 10 11 14 15) are impossible becuase first character cannot

  be both a space and a quote and first quote location cannot be both

  one and greater one.

----------------------------------------------------------------------*/

*----------------------------------------------------------------------;

* Protect strings that look like numbers or have leading spaces. ;

*----------------------------------------------------------------------;

  do over _c_;

    if _c_ ^= ' ' then do;

      _x_=index(_c_,'"');

      _x_=8*(_c_=:' ')+4*(_x_>1)+2*(_x_=1)+(0<index(_c_,','));

      if _x_ in (2,12,13) then do;

          _c_=tranwrd(_c_,'"',"'");

          _dquote_+1;

      end;

      if _x_ in (0,1,8,9,12,13) then

         _c_='="'||trim(substr(_c_,1,252))||'"'

      ;

    end;

  end;

  put &vlist;

run;

New Contributor
Posts: 3

Re: help on output to csv by using ods tagset template

I just found out that when using DATA _NULL_, there is no need to add '=" and display from excel from data _null_ display the same results as ods mark up language. At this point data _null_ didn't change the value.

/***data _null_***/

filename test 'c:/test.csv';

data _null_;

set budget;

file test delimiter=',' DSD lrecl=32767;

put (_all_) ("09"x);

run;

The above code may need to modification( may use Tom's code) to solve the comma problem.

Super User
Super User
Posts: 6,842

Re: help on output to csv by using ods tagset template

Having the extra TAB character at the beginning of each value is probably going to make them hard to work with in Excel.

Ask a Question
Discussion stats
  • 11 replies
  • 911 views
  • 4 likes
  • 5 in conversation