How to create a surrogate key or auto increment ID

[DarthSAS]

Hi all

I'm very new to the SAS World, but i have a little experience on t-sql. What i want to do is create a table or a data set with with an unique ID with an auto increment like t-sql do or a sequence like Oracle but with SAS.

I've heard of a surrogate key. Can i create un surrogate key to do this functionality ?

[DerekAdams]

Try using monotonic() in proc sql, i.e monotonic() as xxx or if using a datastep xxx = _n_;

[DarthSAS]

Thank you Derek,

I saw in another thread that the monotonic function is undocumented and can cause unexpected results........

Is that right or i can use it without lost or corrupt data ?

[DerekAdams]

I havent had any issues so far so I can't really comment on that at the moment

[Cynthia_sas]

Hi:
This is the Tech Support note on the use of the MONOTONIC function:
http://support.sas.com/kb/15/138.html

The full text of the note says:

The MONOTONIC() function is not supported in PROC SQL. Using the
MONOTONIC() function in PROC SQL can cause missing or non-sequential
values to be returned.


To me, this means that if you do use the MONOTONIC function, then you can't complain when/if it doesn't return sequential numbers.. I suspect that for simple, simple queries, you might be OK, but for more complex queries that involve joins and where clauses, you might get into the missing or non-sequential values.

My .02,
cynthia

[Peter_C]

I heard a logical explanation for concern about use of "monotonic()".
When an sql platform/optimiser/engine has enough information to multi-thread resolution of a query," monotonic()" may be implemented in parallel across threads. The result would not be what is desired.
For simple platforms and queries, that can be expected to single-thread, monotonic() should be able provide the unique id.

This scenario, where a feature cannot be expected to "universally" work, applies in other parts of SAS. For example the number of observations indicated by the NOBS= option on a SET statement, will be missing (or may even be negative) when the data set is a view or subject to a where clause.

PeterC

[DarthSAS]

Thank you,

Ok, and for the surrogate key. Can i generate this in Enterprise Guide when i'm creating a data set ?

Do you have some docs on how work a surrogate key in SAS ?

thx