2

Trying to make a more simple unique identifier from already existing identifier. Starting with just and ID column I want to make a new, more simple, id column so the final data looks like what follows. There are 1million + id's, so it isnt an option to do if thens, maybe a do statement?

ID NEWid

1234 1

3456 2

1234 1

6789 3

1234 1

user2448666
  • 329
  • 1
  • 6
  • 14

2 Answers2

3

A trivial data step solution not using monotonic().

proc sort data=have;
by id;
run;

data want;
set have;
by id;
if first.id then newid+1;
run;
Joe
  • 62,789
  • 6
  • 49
  • 67
  • I like this solution better. Single data step (and considering you might already sort by ID somewhere else, maybe the only necessary step). – o.h Aug 12 '13 at 15:36
2

using proc sql.. (you can probably do this without the intermediate datasets using subqueries, but sometimes monotonic doesn't act the way you'd think in a subquery)

proc sql noprint;

    create table uniq_id as
    select distinct id
    from original
    order by id
    ;

    create table uniq_id2 as
    select id, monotonic() as newid
    from uniq_id
    ;

    create table final as
    select a.id, b.newid
    from original_set a, uniq_id2 b
    where a.id = b.id
    ;

quit;
scott
  • 2,235
  • 1
  • 14
  • 18
  • Thanks, i'm trying to avoid using proc freq and then exporting results to excel because sooner or later the data will be too large. – user2448666 Aug 12 '13 at 15:05
  • FWIW, monotonic() is undocumented and not considered a 'production' function, so use at your own risk. – Joe Aug 12 '13 at 15:11
  • Right, the question was tagged with proc sql, so i used proc sql. Using a data step and "_ N _" would work as well – scott Aug 12 '13 at 15:15