I'm sorry I am trying my best to explain. I'll try once more. Forget what I had said before.   I have an area folder which contains lots of dates folders (yes they are dates in the MMDDYYYY format), in the dates folder is a prod folder which contains that dates datasets. (As I shown in multiple screenshots). There are around 20 datasets in each folder all with the same name. The dates represent when the datasets data where captured. The dataset names in each prod folder are the same. So there is an entity.sas7bdat in every date->prod folder, as well as allocation.sas7bdat, and risk.sas7bdat, and entity_scenario.sas7bdat etc. %let folder = 12312020; /* Specifying the date I want */ libname test "home/area/&folder/prod"; create table Table1 as select a.id, a.date, a.group from test.allocation a where cats(a.id, a.date, a.group) not in (select cats(id, date, group) from test.risk) ; This is an example of one of the tests I create. Notice how I have to specify which date folder I want to use. I want to make it so I dont need to specify the date, and so this table is created for all the date folders.     The project is to create tests and check data quality. So I use sql to create tables to find possible problems in the datasets from the prod folder and review them in the WORK library which is where the create tables are outputted to.   Right now, I have to manually change the date (as you saw in my let statement in the original post) in order to access multiple date folders and test the datasets in them. I want a way to automate this so I dont have to manually change the date, instead running the program would give me the data for all the dates. There are a lot of dates thats why automating it would be better.     I do not need to combine datasets or anything like that. Just need to test them using my create table sql and reviewing them in the excel I export them too.    Im hesitant to share any of the actual code due to work privacy. All the code examples Ive shown is a mock with different names etc. but the foundation of it is the same.   I hope this made it a more clear. Im still learning SAS so I'm trying to explain it in the best way I can   ... View more

Sorry Im new to SAS programming. Option 1 seems closer. If I could describe the process it would be - As you saw I have area -> dates -> prod -> datasets. Each of these dates have datasets in them. - In my original post I showed how I am using a libname to access dates folder "12312020" and then creating a table using the datasets in that folder. - These tables I create are sent to WORK library. - I want to automate it so the tables I create dont need a libname to specify where to look. I want the tables to be created for every dates folder datasets. I hope that helps. Doing my best trying to explain. I think my original post explained it best. ... View more

Here is an example of my folder structure. Each of the date folders have inside a prod folder which carries all the datasets I want to access when creating a table for each date. ... View more

I would like to use multiple files not just cost.sas7bdat. Here is an example of a table I want to create:   create table Table1 as select a.ENTITY_ID, a.date, a.group from test.cost a where cats(a.ENTITY_ID, a.date, a.group) not in (select cats(ENTITY_ID, date, group) from test.cost_detl) ; As you can see there are multiple files within prod I want to access (cost and cost_detl).   I need a way to access every file within the folders and then be able to create tables based on each folder. So Table1 will be created for date 12312024, 12312023, 12312022, and on for every date folder in area. ... View more

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 ... View more

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. ... View more

Yes, I was able to get it working with @Tom code so thank you for that Tom. Thanks for the feedback will remember that for future posts. ... View more

Sorry Im a bit new to SAS programming so Im not sure what you mean by  "what variables have that information". I'll try to further explain my program and what it does hopefully that helps. Basically I have a proc sql statement which creates tables 1-78 called UT_01, UT_02, etc. These tables are created to the WORK library along with UT_LIST which has the UT_ID (Table names), NR_ERR (number of observations), UT_TEXT (Descriptor). When NR_ERR is 0 the dataset is empty, and so that UT_ID table should not be exported. Hope that helps. ... View more