topic Re: help on output to csv by using ods tagset template in ODS and Base Reporting

help on output to csv by using ods tagset template

mildchili — Wed, 24 Jun 2015 17:11:26 GMT

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;

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

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

cwilson — Thu, 25 Jun 2015 13:40:03 GMT

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.

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

RW9 — Thu, 25 Jun 2015 14:09:43 GMT

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.

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

Tom — Thu, 25 Jun 2015 14:29:30 GMT

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.

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

cwilson — Thu, 25 Jun 2015 14:44:46 GMT

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.

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

RW9 — Thu, 25 Jun 2015 14:52:24 GMT

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.

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

Tom — Thu, 25 Jun 2015 15:29:58 GMT

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;

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

mildchili — Thu, 25 Jun 2015 17:35:00 GMT

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_ .

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

Reeza — Thu, 25 Jun 2015 18:18:51 GMT

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

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

Tom — Thu, 25 Jun 2015 18:27:51 GMT

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;

put &vlist;

run;

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

mildchili — Thu, 25 Jun 2015 20:26:11 GMT

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.

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

Tom — Fri, 26 Jun 2015 12:04:07 GMT

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