BookmarkSubscribeRSS Feed
SASACC
Calcite | Level 5
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
3 REPLIES 3
DBailey
Lapis Lazuli | Level 10
You'll need to give some specifics around how your data are organized. Some examples would be nice as well.
darrylovia
Quartz | Level 8
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;
Peter_C
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3075 views
  • 0 likes
  • 4 in conversation