The SAS Output Delivery System and reporting techniques

Missing comas from values while exporting from SAS to csv

Reply
N/A
Posts: 0

Missing comas from values while exporting from SAS to csv

Hi All,

I am trying to export a SAS table to csv using the Export function in SAS. I have one field called address where it contains the address in the following format: house number, street name, county

For example in SAS table the value is as following: '10, Some street, some county'

When its exported into csv it is shown as: '10somestreetomecounty'

I have set not field restriction, I am just exporting all the fields in table 1 to csv format.

If anyone has experienced this and found a work around, please let me know.

Thanks a lot in advance,
Sree
Super Contributor
Super Contributor
Posts: 3,174

Re: Missing comas from values while exporting from SAS to csv

Share your code and SASLOG output (with SOURCE turned on), please, for useful feedback.

Scott Barry
SBBWorks, Inc.
SAS Super FREQ
Posts: 8,743

Re: Missing comas from values while exporting from SAS to csv

Hi:
This seems a bit strange. I cannot duplicate your results using any of the methods below to create the CSV file (see the program below).

Perhaps your best resource for help would be for you to open a track with Tech Support.

cynthia
[pre]
data address;
length name $15 address $50;
infile datalines dsd;
input name $ age address $;
return;
datalines;
"Anne",12,"100, State Ave., USA"
"Bob",15,"123, Pudding Lane, Gooseland"
;
run;

proc print data=address;
run;

**1) Use ODS CSV;
ods csv file='c:\temp\addr.csv';
proc print data=address noobs;
var name age address;
run;
ods csv close;

**2) Use Proc Export;
proc export data=work.address
outfile= "c:\temp\addr_exp.csv"
dbms=csv replace;
run;

**3) Use a SAS macro program;
** see doc here: ;
/*
http://support.sas.com/documentation/cdl/en/lrdict/59540/HTML/default/a002683390.htm
*/
%ds2csv (data=work.address,
runmode=b,
csvfile=c:\temp\addr_mac.csv);

[/pre]
N/A
Posts: 0

Re: Missing comas from values while exporting from SAS to csv

this may be an example of another "home-grown" utility used beyond its limits.
Here are some of the reasons that form my suspicion
1 For some customers, home-grown methods are cherished.
2 DS2CSV appeared along with base SAS macros only in more recent releases.
3 Now, the DATA step FILE option DSD protects delimiters embedded in data values during "PUT". This provides the best guarantee of "quality" CSV output. However irrc, although available since SAS7, the final "bugette" (http://support.sas.com/kb/4/765.html AND "no protection on unmatched double-quote without embedded delimiter") only got removed in SAS9.1.
4 compressing commas out of data values when writing observations to a file, eliminates the problem of delimiter embedded in data.
5 it may be acceptable to remove commas from "comma-formatted" numeric values. (although I can think of better ways)

In the absence of proper evidence (SAS log) of how the problem is generated by code, I can only guess ;-)


PeterC
SAS Super FREQ
Posts: 8,743

Re: Missing comas from values while exporting from SAS to csv

Hi:
I'm not sure what you mean about %DS2CSV -- it's been around since SAS 6. My sample code illustrated that NONE of my methods resulted in the behavior that was first reported (commas disappearing from the address field).
So, I suspect that you're right -- either its a homegrown method that inappropriately masks commas or ??? -- which is why I recommended the the original poster worked with Tech Support on this issue.
cynthia
N/A
Posts: 0

Re: Missing comas from values while exporting from SAS to csv

Cynthia

I'm sorry to provide duff information! I thought I had read among "what's new in SAS9" that the DS2CSV macro was moved from among the macros of SAS/IntrNet, to among base SAS macros, making it available to the rest-of-us (without that product), in base SAS for the first time I thought, in version 9.1

Of course your recommendation referring the problem to Tech Sup is the only correct recourse without further detail.

PeterC

P.S.
just found in "SAS 9.1 what's new"
http://support.sas.com/onlinedoc/913/getDoc/en/whatsnew.hlp/lrdictwhatsnew900.htm#a002699027 .
SAS Super FREQ
Posts: 8,743

Re: Missing comas from values while exporting from SAS to csv

Peter:
I think you're right, that the macros were made more widely available recently. But, I believe the code was either available or downloadable in earlier versions of SAS (see this SUGI 25 paper:
http://www2.sas.com/proceedings/sugi25/25/aa/25p017.pdf and this SUGI 24 paper shows a web site where the macros could be downloaded:
http://www2.sas.com/proceedings/sugi24/Handson/p158-24.pdf )

I prefer ODS for HTML file creation over %DS2CSV's siblings %TAB2HTM, %OUT2HTM and %DS2HTM -- but they were all hot stuff for a while, since coding HTML is nobody's favorite task.

cynthia
Ask a Question
Discussion stats
  • 6 replies
  • 187 views
  • 0 likes
  • 3 in conversation