SAS: export data to multiple csv file by year

Question

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!

Answer 1

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;

Answer 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

Answer 3

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.