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.
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.
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.
@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;
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
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: