Desktop productivity for business analysts and programmers

Splitting up dataset by variables and exporting to multiple excel workbooks

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Splitting up dataset by variables and exporting to multiple excel workbooks

 

I have a table of multiple variables that I wish to split up to export to different excel workbooks.

PeriodNameCountryCityMiles
201701PeterEnglandLondon45
201701PeterEnglandBirmingham60
201701FrancoisFranceParis15
201701FrancoisFranceMarseille55
201701EdouardFranceLyon90

 

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!


Accepted Solutions
Solution
‎03-27-2017 03:37 AM
Valued Guide
Posts: 505

Re: Splitting up dataset by variables and exporting to multiple excel workbooks

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;

View solution in original post


All Replies
Esteemed Advisor
Posts: 6,636

Re: Splitting up dataset by variables and exporting to multiple excel workbooks

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Grand Advisor
Posts: 9,567

Re: Splitting up dataset by variables and exporting to multiple excel workbooks

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;
Solution
‎03-27-2017 03:37 AM
Valued Guide
Posts: 505

Re: Splitting up dataset by variables and exporting to multiple excel workbooks

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;
New Contributor
Posts: 4

Re: Splitting up dataset by variables and exporting to multiple excel workbooks

Excellent! Works like a charm! 

Thank you Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 220 views
  • 3 likes
  • 4 in conversation