I have a table of multiple variables that I wish to split up to export to different excel workbooks.
Period | Name | Country | City | Miles |
201701 | Peter | England | London | 45 |
201701 | Peter | England | Birmingham | 60 |
201701 | Francois | France | Paris | 15 |
201701 | Francois | France | Marseille | 55 |
201701 | Edouard | France | Lyon | 90 |
For each unique combination of Period/Name/Country I wish to make a table to export to excel, that will be named
"Period - Name - Country".xlsx
I've tried doing it with a macro, but haven't really gotten anywhere.
Help is much appreciated!
Creating workbooks using meta data period, name, and country and fact data city, and miles
If a workbook creation fails the code below will pop up a window that
will allow the op to decide what he wants to do.
If compilation of 'mete data' fails the program will issue a message to the log and stop;
All in one address space.
inspired by
https://goo.gl/GiOwZx
https://communities.sas.com/t5/SAS-Enterprise-Guide/Splitting-up-dataset-by-variables-and-exporting-to-multiple/m-p/343966
SOAPBOX ON
Really do not understand the love of 'proc import/export' and
call execute.
If you cannot use libname consider installing R/Python and
on you local power workstation and us R/Python instread of EG SAS.
SOAPBOX OFF
Really do not understand the love of 'proc import/export' and call execute on this list.
SOAPBOX OFF
HAVE
====
Up to 40 obs WORK.HAVE total obs=5
Obs PERIOD NAME COUNTRY CITY MILES
1 201701 Peter England London 45
2 201701 Peter England Birmingham 60
3 201701 Francois France Paris 15
4 201701 Francois France Marseille 55
5 201701 Edouard France Lyon 90
WANT Three workbooks using meta data
(libname creates an sheet name and a named range)
==================================================
d:/xls/wkb201701-Edouard-France.xlsx
d:/xls/wkb201701-Francois-France.xlsx
d:/xls/wkb201701-Peter-England.xlsx
Here is one of these;
d:/xls/wkb201701-Francois-France.xlsx
+---------------------------------------------------+------------+
| A | B | C | D | E |
----------------------------------------------------+------------+
1 | | | | | |
+------------+------------+------------+------------+------------+
2 | PERIOD | NAME | COUNTRY | CITY | MILES |
3 | 201701 | Francois | France | Paris | 15 |
4 | 201701 | Francois | France | Marseille | 55 |
+------------+------------+------------+------------+------------+
[wkb201701]
WORKING CODE
============
Compilation and error checking DOSUBL to extract meta data into macro arrays
Data step to iterate through meta data
Execution and error checking DOSUBL to create excel sheets
Pop up Window to ask op to continute if iteration fails
FULL SOLUTION
=============
* _ _ _
_ __ ___ __ _| | _____ __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| | < __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_|
;
data have;
infile cards expandtabs truncover;
input Period (Name Country City) (: $20.) Miles;
cards4;
201701 Peter England London 45
201701 Peter England Birmingham 60
201701 Francois France Paris 15
201701 Francois France Marseille 55
201701 Edouard France Lyon 90
;;;;
run;
* just in case you rerun;
%utlfkil(d:/xls/wkb201701-Edouard-France.xlsx);
%utlfkil(d:/xls/wkb201701-Francois-France.xlsx);
%utlfkil(d:/xls/wkb201701-Peter-England.xlsx);
* _ _ _
___ ___ | |_ _| |_(_) ___ _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|
;
data _null_;
if _n_=0 then do;
%let rc=%sysfunc(dosubl('
proc sql noprint;
select
max(put(period,6. -l))
,quote(trim(max(name)))
,quote(trim(max(country)))
into
:period separated by ","
,:name separated by ","
,:country separated by ","
from
have
group
by period, name, country
;quit;
'));
end;
* check to see if compilation eas successful;
%sysfunc(ifc(&rc,%str(put "Compilatiion failed";stop;),%str( )));
array pers[&sqlobs] (&period);
array nams[&sqlobs] $20 (&name);
array cnys[&sqlobs] $20 (&country);
do i=1 to &sqlobs;
call symputx('per',put(pers[i],6.));
call symputx('nam',nams[i]);
call symputx('cny',cnys[i]);
rc=dosubl('
libname xel "d:/xls/wkb&per.-&nam.-&cny..xlsx";
data xel.wkb&per;
set have(where=(period=&per and name="&nam" and country="&cny"));
run;quit;
libname xel clear;
');
* check if each workbook was created sucessfully;
if rc ne 0 then do;
* popup asking if you want to cintinue;
putlog "workbook wkb&per &nam &cny failed";
window dsn irow=1 rows=12 color=white
#3 @10
"Continue Y/N:" +1 YN $1.;
display dsn;
end;
if yn="N" then stop;
end;
run;quit;
If you use a textual format, you can do it in one data step:
data have;
input Period $ Name $ Country $ City $ Miles;
cards;
201701 Peter England London 45
201701 Peter England Birmingham 60
201701 Francois France Paris 15
201701 Francois France Marseille 55
201701 Edouard France Lyon 90
;
run;
proc sort data=have;
by period name country;
run;
data _null_;
set have;
length filnam $ 100;
filnam = '$HOME/sascommunity/' !! trim(period) !! '_' !! trim(name) !! '_' !! trim(country) !! '.csv';
file out filevar=filnam dlm=',';
by period name country;
if first.country
then do;
put "period,name,country,miles";
end;
put
period
name
country
miles
;
run;
make a macro and call execute();
data have;
infile cards expandtabs truncover;
input Period (Name Country City) (: $20.) Miles;
cards;
201701 Peter England London 45
201701 Peter England Birmingham 60
201701 Francois France Paris 15
201701 Francois France Marseille 55
201701 Edouard France Lyon 90
;
run;
%macro split(Period=, Name=, Country=);
proc export data=have(where=(period=&period and name="&name" and country="&country"))
outfile="/folders/myfolders/&period-&name-&country..xlsx"
dbms=xlsx replace;
run;
%mend;
proc sql;
create table key as
select distinct period,name,country from have;
quit;
data _null_;
set key;
call execute(catt('%split(period=',period,',name=',name,',country=',country,')'));
run;
Creating workbooks using meta data period, name, and country and fact data city, and miles
If a workbook creation fails the code below will pop up a window that
will allow the op to decide what he wants to do.
If compilation of 'mete data' fails the program will issue a message to the log and stop;
All in one address space.
inspired by
https://goo.gl/GiOwZx
https://communities.sas.com/t5/SAS-Enterprise-Guide/Splitting-up-dataset-by-variables-and-exporting-to-multiple/m-p/343966
SOAPBOX ON
Really do not understand the love of 'proc import/export' and
call execute.
If you cannot use libname consider installing R/Python and
on you local power workstation and us R/Python instread of EG SAS.
SOAPBOX OFF
Really do not understand the love of 'proc import/export' and call execute on this list.
SOAPBOX OFF
HAVE
====
Up to 40 obs WORK.HAVE total obs=5
Obs PERIOD NAME COUNTRY CITY MILES
1 201701 Peter England London 45
2 201701 Peter England Birmingham 60
3 201701 Francois France Paris 15
4 201701 Francois France Marseille 55
5 201701 Edouard France Lyon 90
WANT Three workbooks using meta data
(libname creates an sheet name and a named range)
==================================================
d:/xls/wkb201701-Edouard-France.xlsx
d:/xls/wkb201701-Francois-France.xlsx
d:/xls/wkb201701-Peter-England.xlsx
Here is one of these;
d:/xls/wkb201701-Francois-France.xlsx
+---------------------------------------------------+------------+
| A | B | C | D | E |
----------------------------------------------------+------------+
1 | | | | | |
+------------+------------+------------+------------+------------+
2 | PERIOD | NAME | COUNTRY | CITY | MILES |
3 | 201701 | Francois | France | Paris | 15 |
4 | 201701 | Francois | France | Marseille | 55 |
+------------+------------+------------+------------+------------+
[wkb201701]
WORKING CODE
============
Compilation and error checking DOSUBL to extract meta data into macro arrays
Data step to iterate through meta data
Execution and error checking DOSUBL to create excel sheets
Pop up Window to ask op to continute if iteration fails
FULL SOLUTION
=============
* _ _ _
_ __ ___ __ _| | _____ __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| | < __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_|
;
data have;
infile cards expandtabs truncover;
input Period (Name Country City) (: $20.) Miles;
cards4;
201701 Peter England London 45
201701 Peter England Birmingham 60
201701 Francois France Paris 15
201701 Francois France Marseille 55
201701 Edouard France Lyon 90
;;;;
run;
* just in case you rerun;
%utlfkil(d:/xls/wkb201701-Edouard-France.xlsx);
%utlfkil(d:/xls/wkb201701-Francois-France.xlsx);
%utlfkil(d:/xls/wkb201701-Peter-England.xlsx);
* _ _ _
___ ___ | |_ _| |_(_) ___ _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|
;
data _null_;
if _n_=0 then do;
%let rc=%sysfunc(dosubl('
proc sql noprint;
select
max(put(period,6. -l))
,quote(trim(max(name)))
,quote(trim(max(country)))
into
:period separated by ","
,:name separated by ","
,:country separated by ","
from
have
group
by period, name, country
;quit;
'));
end;
* check to see if compilation eas successful;
%sysfunc(ifc(&rc,%str(put "Compilatiion failed";stop;),%str( )));
array pers[&sqlobs] (&period);
array nams[&sqlobs] $20 (&name);
array cnys[&sqlobs] $20 (&country);
do i=1 to &sqlobs;
call symputx('per',put(pers[i],6.));
call symputx('nam',nams[i]);
call symputx('cny',cnys[i]);
rc=dosubl('
libname xel "d:/xls/wkb&per.-&nam.-&cny..xlsx";
data xel.wkb&per;
set have(where=(period=&per and name="&nam" and country="&cny"));
run;quit;
libname xel clear;
');
* check if each workbook was created sucessfully;
if rc ne 0 then do;
* popup asking if you want to cintinue;
putlog "workbook wkb&per &nam &cny failed";
window dsn irow=1 rows=12 color=white
#3 @10
"Continue Y/N:" +1 YN $1.;
display dsn;
end;
if yn="N" then stop;
end;
run;quit;
Excellent! Works like a charm!
Thank you
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: