Adding rows to a data set for each ID

[sascode]

Hello,

 i have an table with repeating id ( because of the recurring events) and i need to add two rows for each observations. Ex if id 1 exist with 4 rows , i need to more rows with same id number which in this case is 1. This should happen for different id i have in my data set.

Thanks.

[ballardw]

Are there any other variables in the data set? If so, what values should they have on the "new" rows?

Example data is likely helpful in showing what you have and what you want.

[sascode]

Great Question,thanks,
there are type and region which i want to be same and in time variable i need to be the value 365.

[ballardw · Accepted solution]

This is one way and obviously untested as no example data was provided.

data want;
   set have;
   by  id;
   if last.id then do;
      output; /*the current value*/
      do ; 
         time=365; /*all other variables will have the value of the 
                     last current observation*/
         output; /*first additional record*/
         output; /*second additional record*/
     end;
   end; /* the do fron the Last.id*/
   else output; /* write the current record when not the last*/
run; 

Normally SAS uses an implicit OUTPUT statement at the bottom of the data step code to write to the output set. Since we want to write conditionally (the extra rows) and have used OUTPUT to do that we need to provide the "else output;" otherwise you would only get the last of the id and the extra rows.

This assumes the data is sorted by ID. When you use a BY  statement in a data step SAS creates additional automatic variables First. and Last. for each variable on the by statement. These have values of 1 (true) or 0 (false) and are not written to the output set. You can test if the current is the First or Last of a group and execute code conditionally based on those values.

It does take some practice to get the right group when there is more than one variable on the by statement.

[sascode]

Hello , looks great,it works,
even that i feel i might ask something else related to this code.Do you mind?
Thanks for helping.

[ballardw]

---

@sascode wrote:
Hello , looks great,it works,
even that i feel i might ask something else related to this code.Do you mind?
Thanks for helping.

---

Please go ahead and ask.

[sascode]

OK,you did the difficult part but i have something else,
now next to time i need to create another variable called entry which met the condition:
when the id starts(in my understanding first.id=1) then entry 0 ,
after entry should be equal to id but previous one.Let say: id has 1 1 1 , time has 100 200 300 ,then entry should look like 0 100 200 ..
Thanks.

[ballardw]

---

@sascode wrote:
OK,you did the difficult part but i have something else,
now next to time i need to create another variable called entry which met the condition:
when the id starts(in my understanding first.id=1) then entry 0 ,
after entry should be equal to id but previous one.Let say: id has 1 1 1 , time has 100 200 300 ,then entry should look like 0 100 200 ..
Thanks.

---

Now you are getting to the part on showing data, best as a data step, to avoid lots of potential confusion. Example should show an example of what you have and then what the desired output would be.

Create data step code that has enough rows, likely 10 or so, and the variables you need to work with and show the start, typically named Have on this forum, and then another one of what you need, typically named Want.For numeric values this pretty easy and here is an example. Best is to write the code  in your SAS editor, copy it, and then on the forum open a code box using the </> icon and pasting the code. The code box is important because the main message windows will reformat text. And sometimes the data step won't run depending on how it is written. Example:

data have;
   input id var1 var2;
datalines;
1 10 12
1 11 22
1 19 45
2 8  36
2 9  50
;

Datalines (or cards) is on a line by itself and ends with the ; the values after that have a semicolon on the line after the last of the data. If you need to indicate the variable has a missing value place a . in that position.

Your want data set would likely start with the Have and just insert rows of values as needed.

This is enough different from the original question that is should be separate topic with a subject better than "SAS" (the forum is about SAS so that subject doesn't help much).