topic Re: How to check if new record is added in a sas dataset in SAS Programming

How to check if new record is added in a sas dataset

SASACC — Tue, 17 May 2011 08:16:20 GMT

Hi ,

I have a requirement in which on daily basis, I have to find if a new record is added in the dataset and if it is added then I have to use that record for further process.

Please let me know how can I find this through SAS code.

Thanks,
SASACC

Re: How to check if new record is added in a sas dataset

DBailey — Tue, 17 May 2011 11:20:09 GMT

You'll need to give some specifics around how your data are organized. Some examples would be nice as well.

Re: How to check if new record is added in a sas dataset

darrylovia — Tue, 17 May 2011 11:27:52 GMT

It all depends on how the new record is inserted into your master table and how big your table is. There are many was to accomplish this functionallity.

I would use any of the 4 methods below:
1) I would create a timestamp and the most recent record would be the max timestamp, additional you can create a new record flag from the transaction set.

2) create a new record flag from the incoming data set
See below for a quick code snippet.

3) create an audit data set

4) create a unique sequential id for your master file and store the max value in another table. All inserted record would be incremented by 1 then any ID greater than the max id would be new.

data master_table;
format timestamp datetime21.2;
ID=1;value='HELLO';timestamp=datetime();output;
ID=2;value='WORLD';timestamp=datetime();output;
run;
proc sql;
create table max_id as
select max(id) as max_id
from master_table;
select max_id
into :max_id
from max_id;
quit;

data transaction_table;
value='TEST ';
run;

data master_table;
set master_table (in=inMaster)
transaction_table (in=inTransaction)
;
* create timestamp to find newest record;
* create 1/0 indicator for newest record;
* increment id by 1;
if inTransaction then do;
counter+1;
timestamp=datetime();
new=(inTransaction);
id=&max_id+counter;
end;
run;

Re: How to check if new record is added in a sas dataset

Peter_C — Wed, 18 May 2011 06:39:06 GMT

hope this helps.
you can easily tell the negative.
When a table is updated, a timestamp in its header is updated. The following code assumes the data set you want to check, is named yourlib.yourmem

Proc sql noprint; select modate format= datetime19. into :latest_update from dictionary.tables where libname= 'YOURLIB' and memname= 'YOURMEM' ;
quit ;
%put latest update was &latest_update ;

You can then judge whether this is before or after the last time you reported.

Having the macro variable &latest_update also allows you to use it as a SAS datetime constant, as in these statements

%put represented as %sysfunc( putn( "&latest_update"dt, twmdy ));
%put just as date %sysfunc( putn( "&latest_update"dt,dtdate9));
%put time since latest update is %sysfunc( range( %sysfunc(datetime()), "&latest_update"dt), time. );

Additional information is available from the same dictionary table, including "number of observations or rows of data in the table". That can be extracted in the same kind of way. However, since this data might be updated with a new row and have an old row deleted, the number of rows might not change when a new row is added, so I assumed the "modified date" would provide the safest indication of change.

peterC