4

I have a dataset in SAS, which contains 20 years of data. I want to export to csv file for each year. Is there any easy way to do it?

Here is what I'm doing for one year now (which is naive):

proc export
data=ds (where=(year=2011)) outfile='ds2011.csv' DBMS=CSV replace;
run;

Thanks a lot!

SDF
  • 71
  • 1
  • 1
  • 4

3 Answers3

7

Non-macro option:

You can use the file statement in a data step to write out various types of text or delimited files. Using the filevar option allows you to create one file for each value of a variable as is done below.

Replace yourdata with your dataset and write_location with where you want the file to go.

data _null_;
  set yourdata;
  length fv $ 200;
  fv = "\write_location\" || TRIM(put(year,4.)) || ".csv";
  file write filevar=fv dsd dlm=',' lrecl=32000 ;
  put (_all_) (:);
run;
DWal
  • 2,752
  • 10
  • 19
  • 3
    It's also worth noting that for large datasets, this approach is much more efficient than the macro version as it only has to read the input dataset once (as opposed to once for each year). – Robert Penridge Feb 02 '15 at 22:36
  • 1
    `put _all_;` results in named output (e.g. "year=2010"), and includes the value of fv in the output, as well as other automatic variables `_N_` and `_ERROR_`. It also does not output the delimiters. The way to avoid this is to use: `put (_all_) (:);`. Here, SAS will print only the values of the variables, and will not include automatic variables. I edited the answer to reflect this cleaner solution. – DWal Feb 03 '15 at 04:20
  • So how is the format going? For example, will the datetime format be ratained? – DaJun Tian Feb 03 '15 at 15:31
  • In this case there are no formats specified, so the `data` step writes to csv using whatever formats are associated with the variables in the dataset. If you have a variable formatted `datetime21.2` in the dataset, it will be written using the `datetime21.2` format in the csv file. – DWal Feb 03 '15 at 16:04
  • 1
    This code is work, but there is no any title or column name in export file. – SY Chen Oct 19 '16 at 08:22
  • @DWal how to add column name to the file? – Lisa Mar 31 '17 at 21:14
2

This would be an ideal job for SAS/Macro, which allows text substitution into repeated code. See below for a solution (assuming a date range of 1991-2011, change the %let statements to suit your range), with a macro %do loop, which substitutes the value of &dt. (1991-2011) into the code provided in your question.

%macro date_export;
%let start_date = 1991;
%let end_date = 2011;

%do dt=&start_date %to &end_date;
  proc export
  data=ds (where=(year=&dt.)) outfile="ds&dt..csv" DBMS=CSV replace;
  run;
%end;
%mend date_export;

%date_export

The macro

The date_export macro is defined between the %macro and %mend statements. Firstly the date range is defined as two macro variables (this can be omitted and the variables plugged straight into the %do statement, but is stated here for clarity). The %do statement starts off a loop, with the dt macro variable ranging between start_date and end_date. This could be rewritten as:

%do dt=1991 %to 2011;

and the initial two %let statements omitted.

The loop runs all code between %do and %end, substituting values of dt when it finds &dt..

Export code

There are a few subtle changes required to ensure the proc export runs correctly inside the macro loop,

  1. Firstly and most obviously I have substituted the year for &dt. which passes the value of dt into your code.

  2. Note that when placed next to the .csv an extra period is required (the first period resolves with the dt, leaving just the single period and csv behind).

  3. The single quotes from your question need to be replaced with double-quotes, so that the macro will resolve. Otherwise the resulting file will be called literally 'ds&dt..csv'.

You can turn on the following options in SAS to assist with macro debugging and visualisation, check the SAS documentation for more info:

SYMBOLGEN
MLOGIC
MPRINT
mjsqu
  • 5,151
  • 1
  • 17
  • 21
0

We have not licensed import and export procedures, so we have to use another approach:

%macro xprtDT(dt=,strt=,nd=);
    %do i=&strt %to &nd;
        filename myfile "C:\&dt&i..csv";
        data _null_;
        set &dt;
        file myfile;
        put 
           @1 var1 z6. 
           @7 ' ' 
           @8 var2 z6. 
           ' '
           ...
           @22 varn;
        run;
    %end;
%mend;
%xprtDT(datadile,1991,2011);

This is little bit complicated, but usable. Each variable contains triplet of informations after @ is position of variable second is name of variable, last is format of variable.

Aesalus
  • 59
  • 1
  • 4