topic Re: Export - CSV File in SAS Programming

Export - CSV File

deleted_user — Mon, 27 Jul 2009 00:25:04 GMT

Hi -

I am able to export my data into a csv file. However, I have multiple companies that make up my dataset along with other variables. I would like to export the data but have each company's data stored in a separate csv file. How would I go about accomplishing this task?

Thank you

Re: Export - CSV File

sbb — Mon, 27 Jul 2009 02:33:52 GMT

For your SAS file, use a WHERE statement to create a "filtered" temporary SAS file having only one value of a particular "company" variable. The use that filtered file to export to a CSV. You can use PROC SQL to generate a set of SAS macro variables, each having a value for the "company" variable, and use a SAS macro approach to repeat the same filter/export code execution for each macro variable condition.

You will find useful information at the SAS support website http://support.sas.com/ either using their website SEARCH or using Google search against the SAS.COM website. Related to this post topic, I have provided one example of a SAS technical conference paper for you to reference:

SAS® Macro Variables and Simple Macro Programs
Steven First, Katie Ronk, Systems Seminar Consultants, Madison, WI
http://www2.sas.com/proceedings/sugi30/130-30.pdf

Scott Barry
SBBWorks, Inc.

Re: Export - CSV File

ChrisNZ — Mon, 27 Jul 2009 03:16:14 GMT

An alternative is to export from a data step and use the filevar= option:
[pre]
data T;
COMPANY='A'; X=1; output;
COMPANY='A'; X=2; output;
COMPANY='B'; X=3; output;

data T1;
set T;
FILE='f:\' || COMPANY || '.txt' ;
file OUT filevar=FILE mod dlm=',';
put COMPANY X;
run;
[/pre]

Re: Export - CSV File

Cynthia_sas — Mon, 27 Jul 2009 21:11:13 GMT

Hi,
Another approach, to those already listed, is to use ODS. ODS CSV allows you to specify a NEWFILE= option, which tells ODS the interval or break point at which a new output file should be started. So, if you use a BY statement, for example, then NEWFILE=BYGROUP would cause a new output file for every BY group.

Here's an example:
[pre]
proc sort data=sashelp.shoes out=shoes;
by region;
where region in ('Asia', 'Canada', 'Pacific');
run;

ods csv file='c:\temp\reg1.csv' newfile=bygroup;
proc print data=shoes;
by region;
var region subsidiary sales inventory returns;
run;
ods csv close;
[/pre]

This code will create 3 output files, REG1.CSV, REG2.CSV, and REG3.CSV -- so if you are OK with the numbered naming convention, then ODS CSV provides another alternative.

cynthia

Re: Export - CSV File

Peter_C — Wed, 29 Jul 2009 11:02:26 GMT

if you were to use a data step FILE statement to create the files, then the FILEVAR= option would allow you to name the files dynamically.
In the on line documentation is an example doing just this, at http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a000171874.htm#a000220945 .
Also in on-line documentation and also in the Forum archives, there should be examples of how to generate csv in a data step, but the simplest (imho) is based on these statements[pre] file yourfile DSD lrecl=10000 ;
set yourData ;
put (_all_)(:) ;[/pre]that PUT statement places the variable data in default formats and in default column order, into csv-format and with protection for any delimiters (commas) emebdded in data values.
To the FILE statement you add the FILEVAR= support.
Extend the SET statement with [pre] BY company ;[/pre]And, [pre] if FIRST.company then do ;
destination = "&path\"!!compress(company)!!'.csv' ;
file yourfile FILEVAR= destination ;
put &headings ;
end ;
[/pre]
well, something like that

The &headings glosses over that issue. Although you can generate a macro variable &headings containing the comma-delimited quoted headings (varnames or var labels when present) with [pre]PROC SQL noprint ;
select quote( quote( trim( coalescec( LABEL, NAME ))))
into :headings separated by " ', ' "
from dictionary.columns
where libname = "%upcase(yourLib)"
and memname = "%upcase(yourMemberName)"
order by varnum
;[/pre]
a neater alternative that creates the heading within the same data step, was demonstrated by the user data _null_; in these Forums at http://support.sas.com/forums/click.jspa?searchID=-1&messageID=18398 .

"how many ways are there, to skin a cat?"

(not that I want to;-)

PeterC
(and, by the way, using this data step method to generate CSV data, is faster than the ODS CSV tagset method which becomes very relevant for volumes in the thousands-of-cells )