topic Splitting dataset into multiple datasets by Entity and Month in SAS Programming

Splitting dataset into multiple datasets by Entity and Month

stormblaster98 — Tue, 28 Jun 2022 15:34:03 GMT

I have a WORK library filled with datasets, within these datasets are data ENTITY_ID, and MONTH.

For each dataset in the WORK library (called UT_01-UT_78), I want to split the original datasets up into multiple datasets sorted by the same ENTITY_ID and MONTH.

Here is an example:

/* Original Dataset Called: UT_01 */
ENTITY_ID  MONTH
Jake       11JAN19
Bob        11DEC17
Jake       11NOV18
Jake       11JAN20
Bob        11DEC18
Jake       11NOV21

/* Split up to new file called: Jake_JAN */
ENTITY_ID  MONTH
JAKE       11JAN19
JAKE       11JAN20

/* Split up to new file called: Jake_NOV */
ENTITY_ID  MONTH
JAKE      11NOV18
JAKE      11NOV21


/* Split up to new file called: Bob_DEC */
ENTITY_ID MONTH
Bob       11DEC18
Bob       11DEC17

The reason I want to split up datasets is to be able to present them in a easier way when I export as an excel file.

I am a SAS beginner so detailed help is appreciated.

Re: Splitting dataset into multiple datasets by Entity and Month

PaigeMiller — Tue, 28 Jun 2022 15:39:57 GMT

Why is this necessary? If you export one great big large Excel file, the user can then use the Filter in Excel to obtain Bob December records. Seems to me to be the easiest solution.

But anyway, this question how to split up large data sets in SAS gets asked probably once a week and a search of this community ought to find lots of examples with code.

Re: Splitting dataset into multiple datasets by Entity and Month

stormblaster98 — Tue, 28 Jun 2022 15:47:44 GMT

There are many tables that must be split with multiple entities and months in each so having it automated before exporting to excel is far better.

Yes I've been reading those however I am stuck on how to sort by ENTITY_ID and MONTH. I am also stuck on how I can split each dataset in the WORK library, right now I am only able to split 1 dataset at a time.

Re: Splitting dataset into multiple datasets by Entity and Month

ballardw — Tue, 28 Jun 2022 16:20:29 GMT

@stormblaster98 wrote:

There are many tables that must be split with multiple entities and months in each so having it automated before exporting to excel is far better.

Yes I've been reading those however I am stuck on how to sort by ENTITY_ID and MONTH. I am also stuck on how I can split each dataset in the WORK library, right now I am only able to split 1 dataset at a time.

Why do you have a problem sorting? Probably because your data is missing an important element. You currently apparently have a date value. If you want to sort by MONTH, not your date, then add a variable that actually contains the month and sort by that variable instead of your date.

If the purpose is to export to a spreadsheet then use ODS Excel for output and use something with the entity_id and actual month variables as BY with proc print.

Maybe something like this which will send each entity_id month combination to a separate sheet in an Excel file.

Note that using MONTH numbers allows sorts to work where MONTH names do no.

data need;
   set ut_01;
   Monthnum = month(month);
run;

proc sort data=need;
   by entity_id monthnum;
run;

ods excel file="<your path>\yourfilename.xlsx"
options (sheet_interval='bygroup');

proc print data=need;
   by entity_id monthnum;
run;

ods excel close;

Re: Splitting dataset into multiple datasets by Entity and Month

HB — Tue, 28 Jun 2022 16:22:58 GMT

"There are many tables that must be split with multiple entities and months in each "
The questions remains, why is this needed? "Must" they be split? If there are multiple tables combine them, export one table and do the presentation level stuff in Excel as PaigeMiller suggests.

Perhaps if you provided more context or something.

Re: Splitting dataset into multiple datasets by Entity and Month

stormblaster98 — Tue, 28 Jun 2022 16:49:24 GMT

I'll try to explain why it is needed. There are 78 tables, each of them have different data. I need to split up each table so that the excel file has more presentable sheets within it. It is not possible to combine each dataset into one big table since the data in each table is different.

Context: My current code creates 78 tables to the WORK library. The goal is to split all of these tables (where necessary) by ID and Month (as shown in my example of the original post) and then export it to an excel file to make it more presentable. So within that excel file will be each original dataset split up by ID and Month (UT_06, UT_06_Jake_DEC, UT_06_Jake_NOV, UT_06_Bob_OCT,...,UT_78, UT_78_Paul_JAN, etc.).

Example of 2 datasets:

/* UT_06 */
ENTITY_ID  MONTH    Origin  Speed
Jake       11JAN19  USA     100 KM/h
.
.
.

/* UT_07 */
QUOTE_ID   DT       GROUP  COST
Farm       11NOV19  Land   $100.00
.
.
.
/* These are rough examples of how the datasets are different */

Sorry if Im not explaining it well, I hope this helped a little

Re: Splitting dataset into multiple datasets by Entity and Month

PaigeMiller — Tue, 28 Jun 2022 17:41:50 GMT

So 78 splits ... the user then has to find the one they want, let's say John August. in that list of 78 ... or the user could have one Excel file and filter in Excel for John & August

Roughly equal in amount of effort for the end user, in my opinion

Tomato ... tomahhto

But lots more effort for the programmer.