topic Re: How to create a surrogate key or auto increment ID in SAS Enterprise Guide

How to create a surrogate key or auto increment ID

DarthSAS — Tue, 04 Aug 2009 16:50:18 GMT

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 ?

Re: How to create a surrogate key or auto increment ID

DerekAdams — Tue, 04 Aug 2009 19:19:03 GMT

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

Re: How to create a surrogate key or auto increment ID

DarthSAS — Tue, 04 Aug 2009 19:27:37 GMT

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 ?

Re: How to create a surrogate key or auto increment ID

DerekAdams — Tue, 04 Aug 2009 20:16:22 GMT

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

Re: How to create a surrogate key or auto increment ID

Cynthia_sas — Tue, 04 Aug 2009 23:31:45 GMT

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

Re: How to create a surrogate key or auto increment ID

DarthSAS — Wed, 05 Aug 2009 12:15:08 GMT

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

Re: How to create a surrogate key or auto increment ID

Peter_C — Wed, 05 Aug 2009 19:39:44 GMT

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