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,
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
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;
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;
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.