BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
6 REPLIES 6
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Share your code and SASLOG output (with SOURCE turned on), please, for useful feedback.

Scott Barry
SBBWorks, Inc.
Cynthia_sas
SAS Super FREQ
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]
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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 .
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 834 views
  • 0 likes
  • 3 in conversation