How to export a SAS dataset to multiple excel workbooks

[dseamdeltarho]

Hi!

I have a dataset that has more than 10000 rows and need to export this dataset to excel workbooks where each workbook represents one country. The total number of countries is 29. For each country, there are events. I also would like to have one event in each tab inside one excel workbook.

I tried this code but it does not work.

%macro export_to_excel;

PROC SQL noprint;
SELECT DISTINCT VENUE_COUNTRYCODE3
INTO :country1-:country99
FROM WORK.datafile;
%let numCountries = &SQLOBS;
QUIT;

%do i = 1 %to &numCountries;
%let country = &&country&i;

ods excel file="filepath&country..xlsx";

PROC SQL noprint;
SELECT DISTINCT EVENT
INTO :event1-:event999
FROM WORK.datafile
WHERE COUNTRY = "&country";
%let numEvents = &SQLOBS;
QUIT;

%do j = 1 %to &numEvents;
%let event = &&event&j;
%let startdate = &&startdate&j;
%let sheetname = %sysfunc(tranwrd(&event, %str( ), _));

ods excel options(sheet_name="&sheetname");
PROC REPORT DATA=WORK.datafile;
WHERE COUNTRY = "&country" AND
EVENT = "&event";
COLUMN EVENT START_DATE END_DATE
CLIENT_FIRST_NAME CLIENT_LAST_NAME
ADDRESS;
RUN;

%end;

ods excel close;

%end;

%mend export_to_excel;

%export_to_excel;

Thanks!

[Tom]

How is it not working?  Did you get errors? Share the first error. Remember to use the Insert Code button to get a pop-up window where you can paste/edit the lines so the forum does not think you are typing paragraphs a text that it should try to make pretty.

A couple of quick changes you should make.

First is remove the inner %DO loop.  Just let ODS EXCEL and PROC REPORT generate the new sheets by using BY group processing.  Check out the SHEET_INTERVAL option of ODS EXCEL.

Second it will work much better to add quotes around the values of country when generating the macro variables.  This will insure the value in the macro variable will match what is in the data and not get confused by leading spaces of macro triggers. 

While you are at it are you sure all of the COUNTRY values are valid as part of a filename?  You could generate the filenames using the same SQL query.

And note that you no longer have to add an upperbound on the number of macro variables to create (unless you want to limit them).

PROC SQL noprint;
SELECT DISTINCT
    quote(trim(VENUE_COUNTRYCODE3),"'")
  , quote(cats("&filepath",compress(VENUE_COUNTRYCODE3),'.xlsx'),"'")
  INTO :country1-
     , :filename1-
  FROM WORK.datafile
;
%let numCountries = &SQLOBS;
QUIT;

Then change the later code to use the new macro variables.

ods excel file=&&&filename&i ;
...
WHERE COUNTRY = &&&country&i ...

[Kurt_Bremser]

Within the country loop, run one PROC REPORT for each country, with event defined as GROUP, and use the SHEET_INTERVAL="BYGROUP" option in the ODS EXCEL statement.

[Reeza]

Changed your code to use sashelp.cars and origin/type as the classification variables. It works fine for me - so it's likely an issue with the data somehow. 

Use the macro debugging options and review the log for the errors. 

options mprint symbolgen;

%macro export_to_excel;
%let dsn=sashelp.cars;

PROC SQL noprint;
SELECT DISTINCT origin
INTO :country1-
FROM &dsn;
%let numCountries = &SQLOBS;
QUIT;


%do i = 1 %to &numCountries;
%let country = &&country&i;


ods excel file="//home/fkhurshed/Demo1/&country..xlsx";


PROC SQL noprint;
SELECT DISTINCT type
INTO :event1-
FROM &dsn.
WHERE origin = "&country";
%let numEvents = &SQLOBS;
QUIT;


%do j = 1 %to &numEvents;
%let event = &&event&j;
%let startdate = &&startdate&j;
%let sheetname = %sysfunc(tranwrd(&event, %str( ), _));


ods excel options(sheet_name="&sheetname");
PROC REPORT DATA=&dsn;
WHERE origin = "&country" AND
type = "&event";
COLUMN Origin type make model;
RUN;

%end;

ods excel close;

%end;

%mend export_to_excel;

%export_to_excel;