Queries to it locks the row, provides the next value, and then unlocks the row, thereby guaranteeing uniqueness. Oracle sequences (and I have done the same in DB2) keep an underlying table with the sequence name and next value. The performance of identifying the maximum value depends on the number of records in the table. * not shown here: code to check if the sequence exists and createĬonnection to Oracle (select &sequence.nextval from dual) The heart of the macro has code like: proc sql noprint Once the sequences are established another macro gets the next sequence val. I recently created a sas macro that uses Oracle passthru to initialise such Oracle sequences from the current maximum value (+ 1) The database guarantees that no numbers are duplicated - which can happen if concurrently executing SAS programs all get the max value from a table and deduce the next number. This is is especially useful is the numbers being sequenced are id columns and the tables may get records added by multiple program which may potentially run in parallel. If you have access to a database like Oracle, then I recommend the use of database sequences to ensure you keep sequences clean. The solution i have come up with doesn't seem very elegant, and I'm worried it will be prone to errors once implemented at a larger scale.Īny thoughts on the likelihood of errors that could occur, or how to improve the solution?Ĭreate table int1 as select *, max(seq_have) as seq_max from example group by id The order of the observations without sequence numbers is arbitrary, as long as it doesn't overlap and is larger then the largest number of the existing sequence inside the group. I have data where i have a broken sequence of numbers (seq_have) where i want to continue the sequence from largest number in the existing sequence for each ID, for the observations with missing sequence numbers.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |