topic Inserting duplicate rows for breaks in data in SAS Procedures

Inserting duplicate rows for breaks in data

Phoenix66 — Fri, 11 May 2012 00:32:26 GMT

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;

Re: Inserting duplicate rows for breaks in data

Linlin — Fri, 11 May 2012 01:59:00 GMT

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

Re: Inserting duplicate rows for breaks in data

Ksharp — Fri, 11 May 2012 02:16:16 GMT

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

Re: Inserting duplicate rows for breaks in data

Haikuo — Fri, 11 May 2012 02:23:45 GMT

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;

Re: Inserting duplicate rows for breaks in data

Linlin — Fri, 11 May 2012 02:57:08 GMT

do scale=1 to 11;

should be

do scale=0 to 11;

Re: Inserting duplicate rows for breaks in data

Ksharp — Fri, 11 May 2012 04:01:59 GMT

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

Re: Inserting duplicate rows for breaks in data

Phoenix66 — Sun, 13 May 2012 21:50:02 GMT

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