topic Re: Splitting up dataset by variables and exporting to multiple excel workbooks in SAS Enterprise Guide

Splitting up dataset by variables and exporting to multiple excel workbooks

cat2 — Fri, 24 Mar 2017 06:32:56 GMT

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!

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

Kurt_Bremser — Fri, 24 Mar 2017 07:48:48 GMT

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;

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

Ksharp — Fri, 24 Mar 2017 10:30:44 GMT

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;

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

rogerjdeangelis — Fri, 24 Mar 2017 14:00:48 GMT

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;

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

cat2 — Mon, 27 Mar 2017 07:36:51 GMT

Excellent! Works like a charm!

Thank you