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;
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
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
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;
do scale=1 to 11;
should be
do scale=0 to 11;
You can't sure the max value is 11,aslo you can't sure id would always be A ?
Thats true, there are multiple ID's and the ending variables differ slightly around the 600 mark.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.