Suppose I have a table called "sourcetable" with colA, colB, colC. I simply want to create an id column with non-repeating, unique and random integers. How can I create this either in netezza sql or using proc sql (SAS)?

1 Answers

Richard On

To ensure uniqueness you will certainly constrain id as either primary key or unique.

The SAS functions UUIDGEN or RANUNI are candidates for generating values with very high probability of uniqueness at computation time. I don't think there is any simple/isolated function that can guarantee 100% it will not compute a value previously returned.

UUIDGEN returns a 36 byte character value such as 170bf2ef-16c7-4b7f-b25b-000333d9679b

RANUNI scaled by by 1E15 will return an integer value with up to 14 digits.

data want;
  do i = 1 to 100;
    id_v1 = uuidgen();
    id_v2 = floor(1e15 * ranuni(0));
  format id_v2 15.;

For Netezza try a look at SO How to overcome Netezza's lack of unique constraint/referential integrity enforcement? "