DI: Using a dynamic table name

[kajal_30]

Hi,

In my DI job I will be using a dynamic table which has timestamp as the suffix in the table name.Could you please help with the best way of doing if with transformation and code  details? abc_31mar2018,abc_30apr2018 etc. 

Thanks

Kajal

[Kurt_Bremser]

I can't be of any help with how to do it in DI Studio, as we do not use it and all our jobs are "hand-written", but I advise you (out of experience) to not use a date9. formatted timestamp. Use either yymmddn8. or yymmddd10. as the format for the timestamp, so that the datasets are automatically sorted in correct sequence within the library (and the file system's directory).

[LinusH]

I'll usually refrain to use such naming logic. This makes DI development and metadata management harder. 

The exception could be to create export data sets/files, which will no used in any further jobs.

But if you use macro variables to set these dates, just use it in the object physical location. Be aware that developers will probably be unable to browse the data in DI Studio to monitor job results.

[ErikLund_Jensen]

Hi @kajal_30 

Don't do it!

A table in DI studio is a SAS metadata Table object with the name you see in DI Studio, an attribute containing the physical table name (the name you see in the file structure with extension .sas7bdat) and an association to a SAS metadata Libname object.

You can write any table in user-written code, but you can't see it or use it in DI Studio before you have made a metadata registration of the table, so you have a SAS metadata Table object pointing to the physical table. It is possible with a piece of user-written code calling Proc Metalib after the table is written, but it is not a very good idea and certainly something your SAS Administrator will be opposed to.

A better solution is to update a table containing historical data. Depending on the amount and type of data you can add a date variable and just append data, or use a SCD2 table loader (or your own code) to update the table. Then you have all your "data sets" in one table, so you can extract the relevant records later.