BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
ChrisNZ
Tourmaline | Level 20
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]
Cynthia_sas
SAS Super FREQ
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
Peter_C
Rhodochrosite | Level 12
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 )

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
  • 4 replies
  • 2543 views
  • 0 likes
  • 5 in conversation