How can I increment my ID

[SaschaD]

Hello,

I tried to add an Id to my table, it works if I start with 1, but how can I increment new line to this table.

It is necessary to keep the old Ids.

DATA work.BSP_2;

    SET work.BSP;

    RETAIN _ID;

    IF Index ~= . THEN _ID = Index;

    ELSE DO;
        _ID+1;
    END;

    DROP ID;

    RENAME _ID = ID;

RUN;

Thanks,

Sascha

[PaigeMiller]

Explain further. Show us a portion of the input data. Show us the desired output.

[ed_sas_member]

Hi @SaschaD 

Could you please post some datalines as well as the expected output?

Thanks,

[SaschaD]

Example:
DATA BSP;
LENGTH Name $20 Firstname $20;
INPUT Name $ Firstname;
DATALINES;
Sample Bernd
Sample Sabine
Sample Georg
Sample Heinz;
RUN;

DATA work.BSP_2;

SET work.BSP;

RETAIN _ID;

IF Index ~= . THEN _ID = Index;

ELSE DO;
_ID+1;
END;

DROP ID;

RENAME _ID = Index;

RUN;

So, now is correct with Index and ID.


I need the highest Id/Index Number to increment +1 ....

[PaigeMiller]

DATA BSP;
LENGTH Name $20 Firstname $20;
INPUT Name $ Firstname;
DATALINES;
Sample Bernd
Sample Sabine
Sample Georg
Sample Heinz;
RUN;

Okay, this does not make sense. Your code specifically refers to a variable named INDEX, which does not appear in this data set. You also need to show us the desired output.

[Tom]

Are you just saying you want to remove the DROP and RENAME statements?  That will keep the original ID and the new one will be named _ID.

[s_lassen]

What does the variable ID do? It looks like you base your new ID on a variable named "Index" not "ID". 

Other than that, I assume that you 

1. want to assign new IDs to rows without an ID
2. want to keep the existing ID (or "Index") values

The program seems to do that, but it depends on the initial values of ID/Index. If your base data contain an ID/Index of 5, then a row with an empty ID/Index, and then a row with an ID/Index of 6, your new data will have two rows with a value of 6. Not good if ID is supposed to be unique.

I do not know what the data (and the ID) is going to be used for, but one other possibility springs to mind: If your existing data have index values e.g. in the range 1 to 1000, use another range for the new values, e.g.:

data BSP_2;
  set BSP;
  if missing(ID) then 
    ID=_N_+10000; /* row number plus high value */
run;  

[SaschaD]

Ok, I need the max ID instead of 10000.

[s_lassen · Accepted solution]

The reason I used 10000 was that it would make all the generated IDs easily identifiable. But if you a sequence from the max ID instead, it can be done like this:

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

data bsp_2;
  set bsp;
  retain new_ID &max_ID;
  if missing(ID) then do;
    new_ID+1;
    ID=new_ID;
    end;
  drop new_ID;
run;

[Shmuel]

You need to update the _ID after every observation to be ready for next one.

I assume that the variable INDEX is your ID;

DATA work.BSP_2;
    SET work.BSP;
    RETAIN _ID;
    IF Index = . then index = _ID;
    ELSE  index = _ID+1;
    _ID = index;  drop _ID; 
 RUN;