BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cat2
Fluorite | Level 6

 

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!

1 ACCEPTED SOLUTION

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

4 REPLIES 4
Kurt_Bremser
Super User

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;
Ksharp
Super User

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

Excellent! Works like a charm! 

Thank you Smiley Happy

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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 4 replies
  • 1715 views
  • 3 likes
  • 4 in conversation