Splitting dataset by a variables value and exporting

[jmmedina252]

Hello, I'm looking to divide a dataset by the value of the site variable in my dataset and export the data tables to separate tabs in excel.  I would like this to be done dynamically because I have about 14 values for the site variable.  The data looks like this:

Site         ID           Date

site 1       100        01JAN23

site 2       100       01JAN23

site 3       150       01JAN23

The outcome would be :

table 1

Site         ID           Date

site 1       100        01JAN23

table 2

Site         ID           Date

site 2       100        01JAN23

table 3

Site         ID           Date

site 3       150        01JAN23

[ballardw]

General approach: by group processing

proc sort data=have;
   by site;
run;

ods excel file="yourpathandfilenamegohere.xlsx"
       options(sheet_interval="BYGROUP");
proc print data=have noobs;
   by site;
ods excel close;

The option Sheet_interval="Bygroup" will create a separate sheet for each level of the BY variable in the Proc Print output.

Note: if you have multiple procedures trying to send related output for a By variable this doesn't work as the different procedures are processing data at different points of the output to ODS cycle.

[jmmedina252]

Hello, I have to sort the data by certain variables, like date as well before it is exported to excel. Is there a way I can do this with this code?

[ballardw]

---

@jmmedina252 wrote:
Hello, I have to sort the data by certain variables, like date as well before it is exported to excel. Is there a way I can do this with this code?

---

Include the date in the sort and have a matching BY statement in proc print. One sheet per combination of of the BY variables.

If you have enough data you might run into spreadsheet limitations on the number of sheets.

[Tom]

Just ask SAS to do it.

Here is an example:

proc sort data=sashelp.class out=class;
   by sex;
run;

ods excel file="c:\downloads\by_sex.xlsx"
 options(sheet_interval="BYGROUP"
         sheet_name="Sex=#byval(sex)"
        )
;
options nobyline;
proc print data=class noobs;
   by sex;
run;
ods excel close;