BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SaschaD
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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;

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
ed_sas_member
Meteorite | Level 14

Hi @SaschaD 

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

Thanks,

SaschaD
Obsidian | Level 7
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
Diamond | Level 26

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.

--
Paige Miller
Tom
Super User Tom
Super User

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
Meteorite | Level 14

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
Obsidian | Level 7
Ok, I need the max ID instead of 10000.
s_lassen
Meteorite | Level 14

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
Garnet | Level 18

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1429 views
  • 0 likes
  • 6 in conversation