DATA Step, Macro, Functions and more

Export - CSV File

Reply
N/A
Posts: 0

Export - CSV File

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
Super Contributor
Super Contributor
Posts: 3,174

Re: Export - CSV File

Posted in reply to deleted_user
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.
PROC Star
Posts: 1,759

Re: Export - CSV File

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]
SAS Super FREQ
Posts: 8,864

Re: Export - CSV File

Posted in reply to deleted_user
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
Valued Guide
Posts: 2,177

Re: Export - CSV File

Posted in reply to deleted_user
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_)(Smiley Happy ;[/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 )
Ask a Question
Discussion stats
  • 4 replies
  • 1213 views
  • 0 likes
  • 5 in conversation