CODEWITHSUNDEEP
sasincrement
peXeq
peXeq

Reputation: 177

Create Index (ID) and increment (SAS)

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 

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;

Upvotes: 0

Views: 803

Answers (2)

Richard
Richard

Reputation: 27508

You need to show more of your code that will demonstrate the problem. The following example is the same as yours, but does not 'fail' to assign a desired index

Example:

data master;
  do name = 'A','B','C';  OUTPUT;  end;
run;

data master;
  set master;
  index = _n_;
run;

data new;
  do name = 'E','F','G';  OUTPUT;  end;
run;

proc sql noprint;
  insert into master(name) select name from new;  * append new rows;
  select max(index) into :next_index from master; * compute highest index known;

data master;
  set master;
  retain next_index &next_index;  * utilize highest index;
  if index = . then do;
    next_index + 1;               * increment highest index before applying;
    index = next_index;
  end;
  drop next_index;                * discard 'worker' variable;
run;

You may have inserted a 1 by accident if the insert statement looked like

insert into master select name, 1 from new;

or the new data already has index set to '1'

insert into master select name, index from new;

Upvotes: 0

imnotarobot
imnotarobot

Reputation: 141

You definied the value of Index column in the first step. Where is a new data that you want set? This code like your data it's work well. Can you share your base datase and the last dataset that you want change? Maybe your data is wrong? (BTW The index variable name is not a lucky choice :-))

data BSP;
    Name="AAC";Value=1000;Index=1;output;
    Name="BTR";Value=500;Index=2;output;
    Name="GRS";Value=250;Index=3;output;
    Name="AAC";Value=100;Index=4;output;
run;
/* the row where Index not definied */
data BSPNew;
    Name="XXX";Value=1000;output;
run;

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

proc append base=BSP data=BSPNew force;
run;

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

data _null_;
    set BSP;
    put Name Value Index;
run ;

/* the result is:
AAC 1000 1
BTR 500 2
GRS 250 3
AAC 100 4
XXX 1000 5
*/

Upvotes: 0

Related Questions