Exporting data from SAS to Excel with a custom file name

Question

I need to export a data set from SAS to Excel 2013 as a .csv file. However, I need the file name to be dynamic. In this instance, I need it to appear as:

in_C000000_013117_65201.csv

where the string, "in_C000000_" will remain constant, the string "013117_" will be the current day's date, and the string "65201" will be the row count of the data set itself.

Any help that you can provide would be much appreciated!

Thanks!

Answer 1

Here's a modified macro I wrote in the past that does almost exactly what you're asking for. If you want to replace sysdate with a date in your desired format, that's easy to do as well:

%let path = [[desired destination]];

%macro exporter(dataset);

    proc sql noprint;
        select count(*) into: obs
        from &dataset.;
    quit;

    data temp;
        format date mmddyy6.;
        date = today();
    run;

    proc sql noprint;
        select date format mmddyy6. into: date_formatted
        from temp;
    quit;

    proc export data = &dataset.
        file = "&path.in_C000000_&date_formatted._%sysfunc(compress(&obs.)).csv"
        dbms = csv replace;
    run;

%mend exporter;

%exporter(your_dataset_here);

Produces datasets in the format: in_C000000_020117_50000.csv