topic Re: Export to multiple Excel files in SAS Programming

Export to multiple Excel files

mlogan — Sat, 04 Feb 2017 16:40:15 GMT

Hi All,

I have a large data set with 110 countries. Now, I need to export the data by country name so that I have a single Excel file for each country. Is there any code/macro anyone can refer me that actually works.

Thanks,

Re: Export to multiple Excel files

iank131 — Sat, 04 Feb 2017 16:46:31 GMT

It would be helpful if you could post some sample data so we write code based on something concrete.

Re: Export to multiple Excel files

art297 — Sat, 04 Feb 2017 17:23:48 GMT

This is definitely not the most efficient way to accomplish the task, but it is a rather straight forward way. You'll have to change the Excel engine to xlsx or whatever works for Excel at your site:

proc sort data=sashelp.class out=temp;
  by sex;
run;

data _null_;
  set temp;
  by sex;
  length forexc $80;
  if first.sex then do;
    forexc=catt("libname export excel 'c:\art\",sex,".xlsx';");
    call execute(forexc);
    call execute('data export.sheet1;');
    forexc=catt('set temp (where=(sex eq "',sex,'"));run;');
    call execute(forexc);
  end;
run;

HTH,

Art, CEO, AnalystFinder.com

Re: Export to multiple Excel files

rogerjdeangelis — Sun, 05 Feb 2017 04:36:42 GMT

HAVE
===
Up to 40 obs WORK.HAVE total obs=15

Obs    COUNTRY    REC

  1    CANADA      1
  2    CANADA      2
  3    CANADA      3
  4    CANADA      4
  5    CANADA      5
  6    GERMANY     1
  7    GERMANY     2
  8    GERMANY     3
  9    GERMANY     4
 10    GERMANY     5
 11    USA         1
 12    USA         2
 13    USA         3
 14    USA         4
 15    USA         5


WANT

d:/xls/CANADA.xlsx
d:/xls/GERMANY.xlsx
d:/xls/USA.xlsx

SHEET CANADA  IN d:/xls/CANADA.xlsx

  +-------------------------+
  |     A      |    B       |
  +-------------------------+
1 |  NAME      |   REC      |
  +------------+------------+
2 | CANADA     |    1       |
  +------------+------------+
   ...
  +------------+------------+
5 | CANADAM    |    5       |
  +------------+------------+

[CANADA]
...


SHEET CANADA  IN d:/xls/USA.xlsx

  +-------------------------+
  |     A      |    B       |
  +-------------------------+
1 |  NAME      |   REC      |
  +------------+------------+
2 |   USA      |    1       |
  +------------+------------+
   ...
  +------------+------------+
5 |   USA      |    5       |
  +------------+------------+

[CANADA]
...


WORKING CODE

    rc=dosubl('
       libname xel "d:/xls/&country..xlsx";
       data xel.&country;
           set have(where=(country="&country"));
       run;quit;
       libname xel clear;
       run;quit;

FULL SOLUTION
============

*create some data;
data have;
 do country="CANADA   ","GERMANY","USA";
   do rec=1 to 5;
     output;
   end;
 end;
run;quit;

%symdel country; *just in case you have this variable in global scope;
data _null_;
  set have;
  by country;
  if first.country then do;
    call symputx('country',compress(country,' .'),'g');
    rc=dosubl('
       libname xel "d:/xls/&country..xlsx";
       data xel.&country;
           set have(where=(country="&country"));
       run;quit;
       libname xel clear;
       run;quit;
    ');
  end;
run;quit;

Re: Export to multiple Excel files

mlogan — Sun, 05 Feb 2017 16:17:39 GMT

Hi rogerjdeangelis,
Thanks, I think I was almost there with your help. But would you please tell me what would be the right code. I am not getting three excel files with three different country name. Thanks.

libname xel "C:\Export to Multiple Excel Files";

DATA xel.Have;
INPUT Country $ Country_Code Region $;
DATALINES;
Japan 107 Asia
USA 109 America
France 103 Europe
Canada 111 America
India 105 Asia
Nepal 115 Asia
Italy 102 Europe
;
RUN;


       libname xel "C:\Export to Multiple Excel Files/&country..xlsx";
       data xel.&country;
           set have(where=(country="&country"));
       run;quit;
       libname xel clear;
       run;quit;

data have;
 do country="CANADA   ","GERMANY","USA";
   do rec=1 to 5;
     output;
   end;
 end;
run;quit;

%symdel country; *just in case you have this variable in global scope;
data _null_;
  set have;
  by country;
  if first.country then do;
    call symputx('country',compress(country,' .'),'g');
    rc=dosubl('
       libname xel "d:/xls/&country..xlsx";
       data xel.&country;
           set have(where=(country="&country"));
       run;quit;
       libname xel clear;
       run;quit;
    ');
  end;
run;quit;

Re: Export to multiple Excel files

rogerjdeangelis — Sun, 05 Feb 2017 18:37:40 GMT

I ran with your data and included the log. You need to sort have by country.

The code below worked with your data. You may get better responses from SAS-L. I cross post interesting posts.

see
https://listserv.uga.edu/cgi-bin/wa?A2=SAS-L;22c6ba79.1702a

and
https://listserv.uga.edu/cgi-bin/wa?A2=SAS-L;eac1033b.1702a

see also

* delete the excel files if they exist;
* you can do this by hand;
%utlfkil(d:/xls/CANADA.xlsx);
%utlfkil(d:/xls/GERMANY.xlsx);
%utlfkil(d:/xls/USA.xlsx);

DATA Have;
INPUT Country $ Country_Code Region $;
DATALINES;
Japan 107 Asia
USA 109 America
France 103 Europe
Canada 111 America
India 105 Asia
Nepal 115 Asia
Italy 102 Europe
;
RUN;

proc sort data=have out=have;
by country;
run;quit;

%symdel country;
data _null_;
  set have;
  by country;
  if first.country then do;
    call symputx('country',compress(country,' .'),'g');
    rc=dosubl('
       libname xel "d:/xls/&country..xlsx";
       data xel.&country;
           set have(where=(country="&country"));
       run;quit;
       libname xel clear;
       run;quit;
    ');
  end;
run;quit;



1517  DATA Have;
1518  INPUT Country $ Country_Code Region $;
1519  DATALINES;

NOTE: The data set WORK.HAVE has 7 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              359.15k
      OS Memory           19948.00k
      Timestamp           02/05/2017 10:31:36 AM
      Step Count                        141  Switch Count  0


1527  ;
1528  RUN;
1529  proc sort data=have out=have;
1530  by country;
1531  run;

NOTE: There were 7 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.HAVE has 7 observations and 3 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.01 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              570.84k
      OS Memory           19948.00k
      Timestamp           02/05/2017 10:31:36 AM
      Step Count                        142  Switch Count  0


1531!     quit;
1532  %symdel country;
1533  data _null_;
1534    set have;
1535    by country;
1536    if first.country then do;
1537      call symputx('country',compress(country,' .'),'g');
1538      rc=dosubl('
1539         libname xel "d:/xls/&country..xlsx";
1540         data xel.&country;
1541             set have(where=(country="&country"));
1542         run;quit;
1543         libname xel clear;
1544         run;quit;
1545      ');
1546    end;
1547  run;

SYMBOLGEN:  Macro variable COUNTRY resolves to Canada
NOTE: Libref XEL was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: d:/xls/Canada.xlsx
SYMBOLGEN:  Macro variable COUNTRY resolves to Canada
SYMBOLGEN:  Macro variable COUNTRY resolves to Canada
NOTE: There were 1 observations read from the data set WORK.HAVE.
      WHERE country='Canada';
NOTE: The data set XEL.Canada has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              1085.43k
      OS Memory           19948.00k
      Timestamp           02/05/2017 10:31:41 AM
      Step Count                        143  Switch Count  0


NOTE: Libref XEL has been deassigned.
SYMBOLGEN:  Macro variable COUNTRY resolves to France
NOTE: Libref XEL was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: d:/xls/France.xlsx
SYMBOLGEN:  Macro variable COUNTRY resolves to France
SYMBOLGEN:  Macro variable COUNTRY resolves to France
NOTE: There were 1 observations read from the data set WORK.HAVE.
      WHERE country='France';
NOTE: The data set XEL.France has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              1085.68k
      OS Memory           19948.00k
      Timestamp           02/05/2017 10:31:41 AM
      Step Count                        143  Switch Count  0


NOTE: Libref XEL has been deassigned.
SYMBOLGEN:  Macro variable COUNTRY resolves to India
NOTE: Libref XEL was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: d:/xls/India.xlsx
SYMBOLGEN:  Macro variable COUNTRY resolves to India
SYMBOLGEN:  Macro variable COUNTRY resolves to India
NOTE: There were 1 observations read from the data set WORK.HAVE.
      WHERE country='India';
NOTE: The data set XEL.India has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              1085.68k
      OS Memory           19948.00k
      Timestamp           02/05/2017 10:31:42 AM
      Step Count                        143  Switch Count  0


NOTE: Libref XEL has been deassigned.
SYMBOLGEN:  Macro variable COUNTRY resolves to Italy
NOTE: Libref XEL was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: d:/xls/Italy.xlsx
SYMBOLGEN:  Macro variable COUNTRY resolves to Italy
SYMBOLGEN:  Macro variable COUNTRY resolves to Italy
NOTE: There were 1 observations read from the data set WORK.HAVE.
      WHERE country='Italy';
NOTE: The data set XEL.Italy has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              1085.68k
      OS Memory           19948.00k
      Timestamp           02/05/2017 10:31:42 AM
      Step Count                        143  Switch Count  0


NOTE: Libref XEL has been deassigned.
SYMBOLGEN:  Macro variable COUNTRY resolves to Japan
NOTE: Libref XEL was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: d:/xls/Japan.xlsx
SYMBOLGEN:  Macro variable COUNTRY resolves to Japan
SYMBOLGEN:  Macro variable COUNTRY resolves to Japan
NOTE: There were 1 observations read from the data set WORK.HAVE.
      WHERE country='Japan';
NOTE: The data set XEL.Japan has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              1085.68k
      OS Memory           19948.00k
      Timestamp           02/05/2017 10:31:42 AM
      Step Count                        143  Switch Count  0


NOTE: Libref XEL has been deassigned.
SYMBOLGEN:  Macro variable COUNTRY resolves to Nepal
NOTE: Libref XEL was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: d:/xls/Nepal.xlsx
SYMBOLGEN:  Macro variable COUNTRY resolves to Nepal
SYMBOLGEN:  Macro variable COUNTRY resolves to Nepal
NOTE: There were 1 observations read from the data set WORK.HAVE.
      WHERE country='Nepal';
NOTE: The data set XEL.Nepal has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              1085.68k
      OS Memory           19948.00k
      Timestamp           02/05/2017 10:31:42 AM
      Step Count                        143  Switch Count  0


NOTE: Libref XEL has been deassigned.
SYMBOLGEN:  Macro variable COUNTRY resolves to USA
NOTE: Libref XEL was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: d:/xls/USA.xlsx
SYMBOLGEN:  Macro variable COUNTRY resolves to USA
SYMBOLGEN:  Macro variable COUNTRY resolves to USA
NOTE: There were 1 observations read from the data set WORK.HAVE.
      WHERE country='USA';
NOTE: The data set XEL.USA has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              1085.68k
      OS Memory           19948.00k
      Timestamp           02/05/2017 10:31:43 AM
      Step Count                        143  Switch Count  0


NOTE: Libref XEL has been deassigned.
NOTE: There were 7 observations read from the data set WORK.HAVE.
NOTE: DATA statement used (Total process time):
      real time           6.73 seconds
      user cpu time       0.24 seconds
      system cpu time     0.35 seconds
      memory              1085.68k
      OS Memory           19948.00k
      Timestamp           02/05/2017 10:31:43 AM
      Step Count                        143  Switch Count  7

1547!     quit;

Re: Export to multiple Excel files

mlogan — Sun, 05 Feb 2017 20:47:41 GMT

Thanks rogerjdeangelis,
Everything worked except the first three lines (the lines that should delete the excel files). I am on the latest version of SaS. Do you think the code has changed? I am getting the following error:

WARNING: Apparent invocation of macro UTLFKIL not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro UTLFKIL not resolved.
193 %utlfkil(d:/xls/GERMANY.xlsx);
-
180

%utlfkil(d:/xls/Canada.xlsx);
%utlfkil(d:/xls/GERMANY.xlsx);
%utlfkil(d:/xls/USA.xlsx);

DATA Have;
INPUT Country $ Country_Code Region $;
DATALINES;
Japan 107 Asia
Japan 117 Asia
USA 109 America
France 103 Europe
Canada 111 America
India 105 Asia
Nepal 115 Asia
Italy 102 Europe
;
RUN;

proc sort data=have out=have;
by country;
run;quit;

%symdel country;
data _null_;
set have;
by country;
if first.country then do;
call symputx('country',compress(country,' .'),'g');
rc=dosubl('
libname xel "d:/xls/&country..xlsx";
data xel.&country;
set have(where=(country="&country"));
run;quit;
libname xel clear;
run;quit;
');
end;
run;quit;