SAS - Exporting same dataset to multiple excel files

Question

I would like to export the dataset to multiple excel files based on a certain variable:

proc sql;
    create table try as
    select distinct make from sashelp.cars;
quit;

proc sql;
    create table try2 as
    select count(make) as aaa from sashelp.cars;
quit;

data _null_;
    set try;
    by make;
    call symputx ('make',compress(make,' .'),'g');
run;

data _null_;
    set try2;
    call symputx('n',aaa);
run;

%macro a;
    %do i=1 %to &n;
        %let var= %scan(&make,&i,"@");
        proc export data=testing (where=(make="&make."))
                outfile="C:\Users\&make..xlsx"
                dbms=xlsx replace;
            sheet="&make." ;
        run;
    %end;
%mend ;
%a;

My goal is to get all the 38 excel files with the maker name as the filename.

However, all I am able to get here is the last maker name's file.

Would you please point out where I am missing out here? Many thanks!!

Dirk Horsten Dirk Horsten · Accepted Answer · 2020-05-22T09:02:50

Your first error is that you count the number of cars that have a make, while you should count the distinct makes of cars. Now let me also take the opportunity to explain you the into clause of sql, so you don't need that data step anymore

proc sql;
    select count(distinct make) 
    into :make_count
    from sashelp.cars;
quit;

You remove blanks and point from your make names, but you better remove all non-alphabetic characters at once, with compress(make, '', 'ka'), in which the options k stands for keep and a stands for alphabetic.

Your main error is that you think you append all make names in the macro variable make, but you actually overwrite make time and again: first you write "Cadillac" to it, then "Chevrolet" and by the time you ever use it, it became "Volvo". I could explain you how to correct your datastep, but instead, I will learn you an option of that into statement:

proc sql;
    select distinct compress(make, '', 'ka')` 
    into :make_list separated by ' '
    from sashelp.cars;
quit;

The rest is easy.

%macro export_by_make;
    %do make_nr=1 %to &make_count;
        %let make= %scan(&make_list, &make_nr);
        proc export data=sashelp.cars (where=(compress(make, '', 'ka')`="&make."))
                outfile="C:\Users\&make..xlsx"
                dbms=xlsx replace;
            sheet="&make." ;
        run;
    %end;
%mend;
%export_by_make;

Note that you don't need to specify a separator for the %scan function, as we separated by blanks, but anyway, if you do, as you use the macro version of scan, you don't need the quotes around it.

SAS - Exporting same dataset to multiple excel files

1 Answers