topic Re: How to make a primary key for multiple instances of an object? in SAS Programming

How to make a primary key for multiple instances of an object?

helloagainoh2 — Mon, 29 Mar 2021 18:33:48 GMT

Lets say I have a data set consisting of names and I would like to assign them a unique id.

Tom

Mary

Jill

Tom

How can I make sure tom gets the same id even though he shows up twice in the data set?

Re: How to make a primary key for multiple instances of an object?

PeterClemmensen — Mon, 29 Mar 2021 18:41:14 GMT

You could do this

data have;
input name $;
datalines;
Tom  
Mary 
Jill 
Tom  
;

data want;
   if _N_ = 1 then do;
      dcl hash h();
      h.definekey('name');
      h.definedata('id');
      h.definedone();
   end;

   set have;

   if h.find() ne 0 then do;
      id + 1; 
      h.add();
   end;
run;

Result:

Obs name id 
1   Tom  1 
2   Mary 2 
3   Jill 3 
4   Tom  1

Re: How to make a primary key for multiple instances of an object?

PeterClemmensen — Mon, 29 Mar 2021 18:45:07 GMT

Also, if you don't care that the ID's are increasing, starting from one, you could do something like this

data have;
input name $;
datalines;
Tom  
Mary 
Jill 
Tom  
;

data want;
   set have;
   id = input(md5(name), pib3.);
run;

Re: How to make a primary key for multiple instances of an object?

Reeza — Mon, 29 Mar 2021 19:01:03 GMT

Create a lookup table and make a format that can be applied to all tables.

Example is here:

https://gist.github.com/statgeek/fd94b0b6e78815430c1340e8c19f8644