2

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.

Rookatu
  • 1,487
  • 3
  • 21
  • 50

2 Answers2

3

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!

Stig Eide
  • 1,052
  • 7
  • 14
  • So upon changing the value of the macro variable, the physical table would point to a new table? – Rookatu Jul 15 '15 at 17:44
  • Where are you putting this code? In the precode of the job, or in a user written code transformation? The latter didn't work for me. Thanks :) – Rookatu Jul 17 '15 at 13:50
  • In the autoexec. If you put the code in the precode for the job, it will work, but view table will not work, because it does not use the precode of the job. – Stig Eide Jul 20 '15 at 06:36
  • So this method requires me to set the value of the macro variable each day by updating an autoexec file? I haven't worked with autoexec files before, and this seems a little involved. Is there no way to automate a job to run each day and, based on the current date, create a string which is the name of the table to be registered then register the table using that string? Also, I don't understand how the registration of a table could work but viewing the table wouldn't ... these actions are job independent. Thanks – Rookatu Jul 27 '15 at 13:56
  • No, you do not set the variable manually, you write a program that does it dynamically. – Stig Eide Jul 28 '15 at 09:59
  • Okay, but this is done by uploading an autoexec file, is that right? Do you think you could elaborate a bit on your answer? The procedure you are suggesting isn't exactly clear to me. Thanks! – Rookatu Jul 28 '15 at 18:18
  • I am sorry if I was unclear. Please see the example in the answer. You need to edit the autoexec file, do you have permissions to do this? If not, you should talk to the administrators. – Stig Eide Jul 29 '15 at 13:32
0

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

sushil
  • 1,576
  • 10
  • 14