topic Re: Splitting dataset by a variables value and exporting in SAS Programming

Splitting dataset by a variables value and exporting

jmmedina252 — Wed, 01 Mar 2023 14:50:34 GMT

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

Re: Splitting dataset by a variables value and exporting

ballardw — Wed, 01 Mar 2023 15:17:46 GMT

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.

Re: Splitting dataset by a variables value and exporting

jmmedina252 — Thu, 23 Mar 2023 15:08:42 GMT

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?

Re: Splitting dataset by a variables value and exporting

ballardw — Thu, 23 Mar 2023 15:36:04 GMT

@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.

Re: Splitting dataset by a variables value and exporting

Tom — Thu, 23 Mar 2023 22:02:02 GMT

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;