Inserting duplicate rows for breaks in data

[Phoenix66]

Hi,

I have a data set that looks similar to this;

Scale     ID     Value

-13     A     123

-12     A     473

-11     A     391

-6     A     127

-5     A     237

-4     A     342

-2     A     186

3     A     98.9

4     A     343

5     A     93

6     A     162

10     A     735

11     A     345

etc

How can I insert duplicate rows so I have a continuous scale of 0 to 11 without affecting any of the negative data, eg transform the data into;

Thanks in advance.

Scale     ID     Value

-13     A     123

-12     A     473

-11     A     391

-6     A     127

-5     A     237

-4     A     342

-2     A     186

0    A     186

1    A     186

2     A     186

3     A     98.9

4     A     343

5     A     93

6     A     162

7    A     162

8    A     162

9    A     162

10     A     735

11     A     345

etc (Later on in the data it moves to ID B etc.)

So where new rows are inserted into the data it copies the values of the row above, except for scale which increase;

[Linlin]

try this:

data have;

input Scale     ID $    Value;

cards;

-13     A     123

-12     A     473

-11     A     391

-6     A     127

-5     A     237

-4     A     342

-2     A     186

3     A     98.9

4     A     343

5     A     93

6     A     162

10     A     735

11     A     345

;

data temp;

  id="A";

  do scale= 0 ,1,2,7,8,9;

  output;end;

  run;

  data want(drop=t);

  retain t;

  set have temp;

  by scale;

  t=ifn(value=.,t,value);

  value=t;

proc print;run;

                         Obs    Scale    ID    Value

                              1     -13     A     123.0

                              2     -12     A     473.0

                              3     -11     A     391.0

                              4      -6     A     127.0

                              5      -5     A     237.0

                              6      -4     A     342.0

                              7      -2     A     186.0

                              8       0     A     186.0

                              9       1     A     186.0

                             10       2     A     186.0

                             11       3     A      98.9

                             12       4     A     343.0

                             13       5     A      93.0

                             14       6     A     162.0

                             15       7     A     162.0

                             16       8     A     162.0

                             17       9     A     162.0

                             18      10     A     735.0

                             19      11     A     345.0

[Ksharp]

EASY.

data have;
input Scale     ID $    Value ;
cards;
-13     A     123
-12     A     473
-11     A     391
-6     A     127
-5     A     237
-4     A     342
-2     A     186
3     A     98.9
4     A     343
5     A     93
6     A     162
10     A     735
11     A     345
;
run;
data want(drop=i _scale);
 merge have have(firstobs=2 keep=scale rename=(scale=_scale));
 output;
 if scale lt 0 and _scale ge 0 then do;
  do i=0 to _scale-1 ;
   scale=i;output;
  end;
 end;
  else if scale ge 0 and _scale ge 0 then do;
     do i=scale+1 to _scale-1 ;
      scale=i;output;
     end;
  end;
run;

Ksharp

[Haikuo]

Easier. LOL.

data have1;

  do scale=1 to 11;

     id='A';

     output;

  end;

run;

data want (drop=_:);

merge have have1;

by scale id;

retain _value;

value=coalesce(value,_value);

output;

_value=value;

run;

[Linlin]

do scale=1 to 11; 

should be

do scale=0 to 11;

[Ksharp]

You can't sure the max value is 11,aslo you can't sure id would always be A ?

[Phoenix66]

Thats true, there are multiple ID's and the ending variables differ slightly around the 600 mark.