BookmarkSubscribeRSS Feed
mlogan
Lapis Lazuli | Level 10

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,

6 REPLIES 6
iank131
Quartz | Level 8
It would be helpful if you could post some sample data so we write code based on something concrete.
art297
Opal | Level 21

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

 

rogerjdeangelis
Barite | Level 11
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;



mlogan
Lapis Lazuli | Level 10

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;
rogerjdeangelis
Barite | Level 11
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;


mlogan
Lapis Lazuli | Level 10

Thanks ,
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2152 views
  • 0 likes
  • 4 in conversation