topic Re: How to check if a dataset is updated for a particular date in SAS Programming

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

santosh_pat69 — Mon, 09 May 2016 16:49:12 GMT

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!!!!

Re: ned to check if the dataset is updated for a particular date

Reeza — Mon, 09 May 2016 16:48:39 GMT

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

Re: ned to check if the dataset is updated for a particular date

santosh_pat69 — Mon, 09 May 2016 20:09:40 GMT

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;

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

LinusH — Mon, 09 May 2016 20:24:40 GMT

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

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

santosh_pat69 — Mon, 09 May 2016 20:31:15 GMT

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.

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

Ksharp — Tue, 10 May 2016 02:37:24 GMT

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.

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

Ksharp — Tue, 10 May 2016 02:45:34 GMT

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;