topic Re: Create Index (ID) and increment in SAS Programming

Create Index (ID) and increment

SaschaD — Thu, 18 Jun 2020 10:25:15 GMT

Hello,

I have a very simple table with sale information.

Name | Value | Index
AAC | 1000 | 1
BTR | 500 | 2
GRS | 250 | 3
AAC | 100 | 4

I add a new column Name Index. And I run the first time the following code

DATA BSP;
Index = _N_;
SET BSP;
RUN;

This works fine for the first time. But now I add more and more sales items and the new line should be get a new indexnumber. The highest index + 1 .... The old sales should keep the indexnumber. But if I run the code below all new lines get the index = 1. What is wrong with the code.

proc sql noprint;
  select max(Index) into :max_ID from WORK.BSP;
quit;

DATA work.BSP;
    SET work.BSP;
  RETAIN new_Id &max_ID;    
  IF Index = . THEN DO;
        new_ID + 1;
        index = new_id;
END;
RUN;

Thanks, 
Sascha

Re: Create Index (ID) and increment

ChrisNZ — Thu, 18 Jun 2020 10:39:51 GMT

I don't see any data being added. Is that done in a prior step?

How is your logic not working?

If you never delete records, this is simple:

data BASE;
  X=1;
  do INDEX=1 to 50;
   output;
  end;
run;
data ADD;
  X=2;
run;
data BASE;
  set BASE ADD(in=A);
  if A then INDEX=_N_;
run;

Re: Create Index (ID) and increment

s_lassen — Thu, 18 Jun 2020 10:42:43 GMT

I think the problem is that you do not drop the NEW_ID variable. So if you run the same code multiple times, the calculated NEW_ID value will be overwritten by the value on the old dataset.

Re: Create Index (ID) and increment

ChrisNZ — Thu, 18 Jun 2020 10:46:37 GMT

If you might have deleted records, something like this.

Not the most efficient but compact.

data BASE;
  X=1;
  do INDEX=1 to 50;
   output;
  end;
run;
data ADD;
  X=2; output;
  X=3; output;
run;
data BASE;
  set BASE(in=B) ADD;
  if B then MAX=max(MAX,INDEX);
  else MAX+1;
  INDEX=coalesce(INDEX,MAX);
run;