0

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)?

double-beep
  • 5,031
  • 17
  • 33
  • 41

1 Answers1

1

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));
    output;
  end;
  format id_v2 15.;
run;

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

Richard
  • 25,390
  • 3
  • 25
  • 38
  • How can this be done using proc sql? My data set is over 2 billion records and I need to write this code with sql –  Mar 14 '19 at 14:50
  • Is there a specific reason you can't use a serial number returned from a sequence ? Sounds more like a question for Netezza experts if you have 2B records Netezza side. Not sure how you get such a large table in a presumed DW without a unique row identifier already baked into the design – Richard Mar 14 '19 at 15:54