How to check if a dataset is updated for a particular date

[santosh_pat69]

Hi,

I am trying to check if the table is updated for a particular date.

Once the data is available for the mentioned date an email need to triggered to a list of users, else need to check after a couple of hours.

Please help!!!!

[Reeza]

How would you know if it's updated? Is there a field to check? Do you need to check the created date?

[santosh_pat69]

Hi Reeza,

we have  field called CNTRL_DTE i have tried using this

%let Tdy= 01012016; /*Some dummy date used here*/

If cntrl_Dte= "&tdy." and obs = 0 then do;

put Note:"Check the table for updates after sometime";

End;

Else Do;

File email EM;

End;

Run;

for some reason this is not working sometimes this sends the email for the updates and some times hte log says

this condition

If cntrl_Dte= &tdy. and obs = 0 is false; 

[LinusH]

How is the table updated in the first place? Perhaps it's easier to automate the mail sending in that step/process?

[santosh_pat69]

Hi

The data is extracted from a teradata table, there a jobs scheduled.

the RDBMS jobs keep executing, periodically and the tables are updated as and when the data is avaialble.

the dataset which we are using to generate the report is most of the times updated by 14.00, and if the reports dosent contains the data for the date we are looking for, we check it in a couple of hours manually the data will be availabe by that time. and we re-generate the report once the data is loaded.

kindly suggest.

[Ksharp]

Try create an Integrity Constraint and a AUDIT type dataset.

data class;
 set sashelp.class;
run;

proc datasets library=work nolist nodetails;
modify class;
ic create age=check(where=(age between 10 and 80))
 message='age must be between 10 and 80';
audit class;
initiate;
quit;


proc sql;
 update class
 set age=20
 where sex='F';
quit;

proc print data=class(type='audit');run;

The following will give you two obs for each and every obs which is updated. One is original obs, Another is the updated obs.

[Ksharp · Accepted solution]

Or you don't need create Integrity Constraint, just an Audit Dataset.

data class;
 set sashelp.class;
run;

proc datasets library=work nolist nodetails;
audit class;
initiate;
quit;


proc sql;
 update class
 set age=200
 where sex='F';
quit;

proc print data=class(type='audit');run;