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
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.
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.
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.
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.
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.
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;
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_ .
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
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;
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.
Having the extra TAB character at the beginning of each value is probably going to make them hard to work with in Excel.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.