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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.