topic Re: SAS Data Integration Studio - transforming all tables in a library in SAS Data Management

SAS Data Integration Studio - transforming all tables in a library

TomekQ — Tue, 31 Mar 2020 07:25:03 GMT

Hi!

I need to perform a transformation on all tables in a library in SAS DIS. How can I do it? Let's say that I want to delete rows based on a certain condition in every table.

Regards

Re: SAS Data Integration Studio - transforming all tables in a library

LinusH — Tue, 31 Mar 2020 12:04:23 GMT

Since DI Studio is quite focused on metadata dependencies, traceability and lineage.

Therefore, it's not so easy/good to build generic jobs or keep generic metadata object (tables).

As a SAS programmer you would probably just build a macro to do this task.

Do the tables have the same structure?

For example if each table represent lets say one month of data, you can have part of the physical table name as macro variable. Then you can build a parameterized job which will have as input (using the loop transformation) a table with a list of tables/dates/etc). One caveat is that you can't open those tables within DI Studio to view the data, because the macro variable is not set in that work space server session that opens the table. There are work-arounds, but none perfect.

Re: SAS Data Integration Studio - transforming all tables in a library

Patrick — Tue, 31 Mar 2020 12:23:58 GMT

@TomekQ

Totally agree with @LinusH : That feels more like some adhoc job you need to support development and not something you'd implement as a regular DIS job in production. If so then just write some SAS code - and If you want to run it out of DIS then copy/paste this code into a DIS user written transformation - and if you want to take it one step further then connect the tables you want to delete rows from as input to the user written transformation and use the generated macros to loop over the connected tables and delete the rows from the tables of your choosing.

....and be aware that any SQL Delete only logically deletes rows so the file size of the physical SAS file won't change.