Dynamically register tables in SAS (DI)

Question

Tables with transaction data are generated daily, with the date in the name e.g. data_01_12_2014. It is clear why this method would be undesirable, but presumably the reason is that the daily tables are enormous and this is a space management mechanism. Whatever the reason, my task is to grab data from these tables, do some transformations, and drop the results into a result table.

My problem is that I want to automate the process, and do not want to manually register the new daily table each day. Is there a way to automate this process in SAS/SAS DI?

Much gratitude.

Answer 1

What I do, is to create a macro variable, and give it the value "01_12_2014". You can then register the table in DI Studio with the physical name name "libref.Data_&datevar." Logical name can be anything. Now the same job will work on the new names, just by changing the value of "datevar" macrovariable.

In the autoexec, a program can be written that sets the macrovariable dynamically. For example, this will set the value to todays date:

data _null_;
  call symputx("datevar",translate(put(today(),DDMMYYD10.),"_","-"));
run;
%put &datevar;

Hope this helps!

Answer 2

I hope i'm not too late in answering the question. Just saw this question today only.

Anyhow, The most important thing that you need to remember is that the registered table showing up on the metadata folder/inventory are just shortcuts to the physical file. Let's say that the DI Studio job that you have is taking input from this table(registered on the metadata server as let's say MYDATA pointing to physical file data_2015_10_30 on 30th October).

On 31st October i can run the below code to update the shortcut to point to 31st dataset i.e data_2015_10_31. The tableID macro value is the Metadata ID of the table which shows in the Basic Properties panel( if it's not showing check View->Basic Properties . It should start showing on bottom left screen). Also, I'm hard coding 2015_10_31, but you can use macro to pick up today's date instead of hard coding. Leaving that to you.

%let tableID=A5LZW6LX.BD000006;
data _null_;
    rc=metadata_setattr("omsobj:PhysicalTable?@Id ='&tableID'",
                        "SASTableName",
                        "DATA_2015_10_31");
    rc=metadata_setattr("omsobj:PhysicalTable?@Id ='&tableID'",
                        "TableName",
                        "DATA_2015_10_31");
run;

PLEASE NOTE THAT DI STUDIO JOB CAN BE OPENED OR CLOSED WHILE YOU MAKE THE CHANGES OR RUN THE ABOVE CODE, BUT IF IT IS OPEN THEN CLOSE IT AND REOPEN IT AND IF THE JOB WAS CLOSED, JUST OPENING IT WOULD WORK. IF YOU DO NOT REOPEN THE JOB THEN TRANSFORMATIONS IN THE JOB WHICH ARE INTERACTING WITH THE DATASET MYDATA WOULD STILL PICK UP OLD TABLE NAME NOT THE UPDATED ONE. Also, The above code CANNOT be added as Precode since opening the job is updating all the linkages of the dataset to the new physical table in the transformations i.e. 31st October in the DI Job. You can created a new job with the above code and add it in the jobflow to run before you main job. If you would like to add it in precode then code to update becomes complicated and lengthy which i would avoid.

Good Reference Link : http://support.sas.com/resources/papers/proceedings09/097-2009.pdf