topic Re: How can I increment my ID in SAS Programming

How can I increment my ID

SaschaD — Mon, 18 May 2020 13:08:38 GMT

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

Re: How can I increment my ID

PaigeMiller — Mon, 18 May 2020 13:17:54 GMT

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

Re: How can I increment my ID

ed_sas_member — Mon, 18 May 2020 13:18:39 GMT

Hi @SaschaD

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

Thanks,

Re: How can I increment my ID

Tom — Mon, 18 May 2020 13:25:51 GMT

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.

Re: How can I increment my ID

s_lassen — Mon, 18 May 2020 13:27:19 GMT

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

want to assign new IDs to rows without an ID
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;

Re: How can I increment my ID

SaschaD — Mon, 18 May 2020 13:32:19 GMT

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 ....

Re: How can I increment my ID

SaschaD — Mon, 18 May 2020 13:35:14 GMT

Ok, I need the max ID instead of 10000.

Re: How can I increment my ID

Shmuel — Mon, 18 May 2020 13:39:46 GMT

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;

Re: How can I increment my ID

PaigeMiller — Mon, 18 May 2020 13:50:21 GMT

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.

Re: How can I increment my ID

s_lassen — Mon, 18 May 2020 13:50:08 GMT

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;