Date: Thu, 25 May 2006 22:48:59 -0500
Reply-To: Jiann-Shiun Huang <Jiann-Shiun.Huang@AMERUS.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jiann-Shiun Huang <Jiann-Shiun.Huang@AMERUS.COM>
Subject: Re: result
Content-Type: text/plain; charset=US-ASCII
Dorian:
The following code should work as you requested and output follows.
data test1;
input time datetime18. price;
format time datetime20.;
cards;
02DEC2002:08:10:04 417.0
02DEC2002:08:10:06 414.0
02DEC2002:08:10:06 414.5
02DEC2002:08:10:15 416.5
02DEC2002:08:10:20 417.5
02DEC2002:08:10:23 417.5
02DEC2002:08:10:24 416.0
02DEC2002:08:10:31 417.0
02DEC2002:08:10:35 416.5
02DEC2002:08:10:35 416.5
02DEC2002:08:10:45 417.0
02DEC2002:08:10:46 415.5
02DEC2002:08:10:51 417.0
02DEC2002:08:11:02 415.0
02DEC2002:08:11:16 415.5
02DEC2002:08:11:29 416.5
02DEC2002:08:11:33 425.0
02DEC2002:08:11:50 416.0
02DEC2002:08:11:51 416.0
02DEC2002:08:12:01 415.5
;
run;
%let seed=12345;
data test2(drop=i);
set test1;
output;
do i=1 to 9;
time=time+(ranuni(&seed)-0.5)*30;
price=price+(ranuni(&seed)-0.5)*30;
output;
end;
run;
proc sort data=test2;
by time;
run;
data YourDataSet;
set test2;
ohecode+1;
run;
%let timeSpan=30;
data NewDataset(keep=ohecode time price altPrice);
format altTime time datetime20.;
retain StartCount 1;
set YourDataset(rename=(ohecode=altOhecode time=altTime
price=altPrice));
ohecode=altOhecode;
time=altTime;
price=altPrice;
i=StartCount;
First=0;
do while (i le nobs);
set YourDataset(rename=(ohecode=altOhecode time=altTime
price=altPrice)) nobs=nobs point=i;
if altTime ge (time+&timeSpan) then leave;
else do;
if altTime gt (time-&timeSpan) then do;
if First eq 0 then do;
First=1;
StartCount=i;
end;
output;
end;
i+1;
end;
end;
run;
proc sort data=NewDataset;
by ohecode time price altPrice;
run;
proc sql;
create table Lead as
select ohecode, price, count(*) as NOBS
from NewDataset
group by ohecode, price
order by ohecode, price;
quit;
%let TrimRatio=0.1;
data Result(keep=ohecode time price trimMean trimStd);
retain NOBS trimCount;
set Lead(rename=(NOBS=oldNOBS)) NewDataset;
by ohecode;
if first.ohecode then do;
NOBS=oldNOBS;
trimCount=NOBS*&TrimRatio;
priceSum=0;
priceSS=0;
obsCount=0;
end;
else do;
obsCount+1;
if (obsCount gt trimCount) and (NOBS-obsCount+1 gt trimCount) then
do;
if (obsCount-1) lt trimCount then do;
priceSum+altPrice*(obsCount-trimCount);
priceSS+altPrice**2*(obsCount-trimCount);
end;
else do;
if (NOBS-obsCount) lt trimCount then do;
priceSum+altPrice*(NOBS-obsCount+1-trimCount);
priceSS+altPrice**2*(NOBS-obsCount+1-trimCount);
end;
else do;
priceSum+altPrice;
priceSS+altPrice**2;
end;
end;
end;
end;
if last.ohecode then do;
temp=NOBS-2*trimCount;
if (NOBS-2*trimCount) gt 0 then
trimMean=priceSum/(NOBS-2*trimCount);
else trimMean=.;
if (NOBS-2*trimCount-1) gt 0 then
trimStd=((priceSS-priceSum**2/(NOBS-2*trimCount))/(NOBS-2*trimCount-1))**0.5;
else trimStd=.;
output;
end;
run;
proc print data=Result noobs;
run;
***** OUTPUT *****
The SAS System 09:27
Thursday, May 25, 2006 3289
trim
ohecode price time Mean
trimStd
1 427.942 02DEC2002:08:09:39 421.430
12.1782
2 399.644 02DEC2002:08:09:39 421.268
11.8339
3 437.503 02DEC2002:08:09:42 420.953
12.0459
4 426.718 02DEC2002:08:09:43 420.953
12.0459
5 417.162 02DEC2002:08:09:45 421.702
11.6413
6 431.540 02DEC2002:08:09:48 421.526
12.2043
7 413.089 02DEC2002:08:09:48 421.187
12.1397
8 408.157 02DEC2002:08:09:49 420.205
12.4670
9 441.913 02DEC2002:08:09:52 419.170
12.3819
10 391.546 02DEC2002:08:09:53 419.539
12.1934
11 429.910 02DEC2002:08:09:54 419.539
12.1934
12 439.463 02DEC2002:08:09:54 419.341
12.0542
13 441.110 02DEC2002:08:09:54 419.281
11.8441
14 431.185 02DEC2002:08:09:55 419.723
11.6697
15 406.059 02DEC2002:08:09:56 419.723
11.6697
16 427.533 02DEC2002:08:09:56 419.723
11.6697
17 425.897 02DEC2002:08:09:57 419.577
12.2284
18 434.825 02DEC2002:08:09:57 419.529
12.0367
19 377.163 02DEC2002:08:09:57 419.529
12.0367
20 424.356 02DEC2002:08:10:00 420.544
12.0377
21 424.511 02DEC2002:08:10:00 420.544
12.0377
22 450.108 02DEC2002:08:10:03 419.888
12.3976
23 383.254 02DEC2002:08:10:03 419.888
12.3976
24 403.645 02DEC2002:08:10:04 419.610
12.6414
25 417.000 02DEC2002:08:10:04 419.605
12.5785
26 412.923 02DEC2002:08:10:04 419.605
12.5785
27 414.837 02DEC2002:08:10:05 419.605
12.5785
28 433.321 02DEC2002:08:10:05 419.683
12.1698
29 442.960 02DEC2002:08:10:05 419.683
12.1698
30 414.000 02DEC2002:08:10:06 419.524
12.0904
31 414.500 02DEC2002:08:10:06 419.524
12.0904
32 427.021 02DEC2002:08:10:07 419.524
12.0904
33 391.773 02DEC2002:08:10:07 419.320
12.0614
34 416.006 02DEC2002:08:10:09 418.918
12.2125
35 417.644 02DEC2002:08:10:10 418.998
12.1012
36 426.291 02DEC2002:08:10:10 418.998
12.1012
37 441.853 02DEC2002:08:10:10 418.998
12.1012
38 414.818 02DEC2002:08:10:11 418.787
12.0821
39 390.166 02DEC2002:08:10:11 418.574
12.0671
40 450.057 02DEC2002:08:10:13 417.822
11.7380
41 423.266 02DEC2002:08:10:13 417.822
11.7380
42 420.397 02DEC2002:08:10:15 417.615
11.5114
43 392.395 02DEC2002:08:10:15 417.620
11.6470
44 409.181 02DEC2002:08:10:15 417.620
11.6470
45 416.500 02DEC2002:08:10:15 417.620
11.6470
46 460.886 02DEC2002:08:10:16 417.740
11.4292
47 437.063 02DEC2002:08:10:17 417.868
11.2489
48 407.565 02DEC2002:08:10:18 417.686
11.2691
49 398.861 02DEC2002:08:10:19 417.743
11.4441
50 402.508 02DEC2002:08:10:19 417.743
11.4441
The SAS System 09:27
Thursday, May 25, 2006 3290
trim
ohecode price time Mean
trimStd
51 409.150 02DEC2002:08:10:20 417.760
11.2830
52 417.500 02DEC2002:08:10:20 417.760
11.2830
53 414.984 02DEC2002:08:10:20 417.852
11.1842
54 388.438 02DEC2002:08:10:21 417.852
11.1842
55 441.821 02DEC2002:08:10:22 417.443
10.7875
56 413.585 02DEC2002:08:10:22 417.443
10.7875
57 417.500 02DEC2002:08:10:23 417.443
10.7875
58 409.822 02DEC2002:08:10:24 417.965
10.7068
59 416.000 02DEC2002:08:10:24 417.702
10.5364
60 416.193 02DEC2002:08:10:24 417.407
10.3189
61 420.351 02DEC2002:08:10:24 417.407
10.3189
62 456.860 02DEC2002:08:10:25 417.510
10.5787
63 438.933 02DEC2002:08:10:26 416.926
10.4077
64 383.422 02DEC2002:08:10:27 417.091
10.4217
65 416.505 02DEC2002:08:10:27 416.991
10.4813
66 425.259 02DEC2002:08:10:27 416.964
10.2087
67 423.353 02DEC2002:08:10:28 416.964
10.2087
68 441.511 02DEC2002:08:10:28 416.972
10.1035
69 454.980 02DEC2002:08:10:29 416.871
10.0337
70 452.123 02DEC2002:08:10:31 416.576
10.1243
71 417.000 02DEC2002:08:10:31 416.576
10.1243
72 406.284 02DEC2002:08:10:31 416.566
10.0186
73 394.348 02DEC2002:08:10:31 416.566
10.0186
74 400.892 02DEC2002:08:10:32 416.447
9.8410
75 388.925 02DEC2002:08:10:33 416.088
9.4893
76 429.598 02DEC2002:08:10:33 416.088
9.4893
77 433.251 02DEC2002:08:10:34 416.243
9.3904
78 404.553 02DEC2002:08:10:34 416.301
9.3123
79 416.500 02DEC2002:08:10:35 416.273
9.5154
80 416.500 02DEC2002:08:10:35 416.273
9.5154
81 429.268 02DEC2002:08:10:35 416.273
9.5154
82 408.760 02DEC2002:08:10:35 415.809
9.1712
83 405.473 02DEC2002:08:10:37 415.758
9.2869
84 401.293 02DEC2002:08:10:39 415.799
9.1982
85 404.961 02DEC2002:08:10:39 415.804
9.2973
86 404.417 02DEC2002:08:10:40 415.596
9.3385
87 404.007 02DEC2002:08:10:41 415.614
9.4393
88 416.015 02DEC2002:08:10:41 415.829
9.3552
89 426.394 02DEC2002:08:10:42 415.735
9.2862
90 409.282 02DEC2002:08:10:42 415.735
9.2862
91 389.873 02DEC2002:08:10:43 415.735
9.2862
92 423.463 02DEC2002:08:10:43 415.735
9.2862
93 416.286 02DEC2002:08:10:43 415.735
9.2862
94 429.354 02DEC2002:08:10:44 415.624
9.2852
95 399.053 02DEC2002:08:10:44 415.624
9.2852
96 409.830 02DEC2002:08:10:44 415.624
9.2852
97 417.018 02DEC2002:08:10:44 415.624
9.2852
98 417.000 02DEC2002:08:10:45 416.174
9.7414
99 427.067 02DEC2002:08:10:46 416.174
9.7414
100 415.500 02DEC2002:08:10:46 416.174
9.7414
The SAS System 09:27
Thursday, May 25, 2006 3291
trim
ohecode price time Mean
trimStd
101 429.560 02DEC2002:08:10:46 416.156
9.6342
102 406.115 02DEC2002:08:10:48 416.322
9.7993
103 440.251 02DEC2002:08:10:48 416.322
9.7993
104 406.264 02DEC2002:08:10:48 416.322
9.7993
105 412.955 02DEC2002:08:10:50 417.240
10.1801
106 414.202 02DEC2002:08:10:50 417.240
10.1801
107 424.909 02DEC2002:08:10:50 417.369
10.3745
108 401.847 02DEC2002:08:10:51 417.647
10.4346
109 417.000 02DEC2002:08:10:51 417.647
10.4346
110 425.111 02DEC2002:08:10:52 417.992
10.7822
111 437.930 02DEC2002:08:10:53 418.021
10.8893
112 429.013 02DEC2002:08:10:54 418.021
10.8893
113 446.896 02DEC2002:08:10:54 418.590
11.6995
114 402.334 02DEC2002:08:10:55 418.590
11.6995
115 400.771 02DEC2002:08:10:55 418.590
11.6995
116 402.591 02DEC2002:08:10:55 418.960
12.0299
117 414.098 02DEC2002:08:10:55 418.991
11.9070
118 419.060 02DEC2002:08:10:56 418.991
11.9070
119 416.203 02DEC2002:08:10:56 418.991
11.9070
120 431.277 02DEC2002:08:10:56 418.733
11.8033
121 413.844 02DEC2002:08:10:57 419.041
11.8718
122 418.291 02DEC2002:08:10:58 419.323
12.4793
123 409.271 02DEC2002:08:10:58 419.040
12.1943
124 407.014 02DEC2002:08:10:59 418.590
11.6701
125 416.470 02DEC2002:08:11:01 418.523
11.5769
126 409.534 02DEC2002:08:11:01 418.984
11.5583
127 415.000 02DEC2002:08:11:02 418.984
11.5583
128 410.085 02DEC2002:08:11:03 419.256
11.4985
129 399.641 02DEC2002:08:11:03 419.256
11.4985
130 416.731 02DEC2002:08:11:03 419.459
11.3925
131 421.948 02DEC2002:08:11:04 419.266
11.4031
132 387.219 02DEC2002:08:11:04 419.919
11.7814
133 421.961 02DEC2002:08:11:04 420.065
11.7141
134 420.989 02DEC2002:08:11:04 420.065
11.7141
135 418.474 02DEC2002:08:11:06 420.275
12.1417
136 395.976 02DEC2002:08:11:07 420.453
11.8960
137 419.752 02DEC2002:08:11:08 420.453
11.8960
138 446.717 02DEC2002:08:11:10 421.720
11.9558
139 410.508 02DEC2002:08:11:10 421.720
11.9558
140 408.921 02DEC2002:08:11:12 422.432
11.8265
141 436.848 02DEC2002:08:11:13 423.424
11.8050
142 450.908 02DEC2002:08:11:14 423.995
11.9953
143 445.587 02DEC2002:08:11:16 423.887
11.8504
144 415.500 02DEC2002:08:11:16 424.036
11.9506
145 454.799 02DEC2002:08:11:17 423.960
12.0804
146 405.761 02DEC2002:08:11:18 424.096
11.8773
147 420.755 02DEC2002:08:11:18 424.096
11.8773
148 444.888 02DEC2002:08:11:18 424.096
11.8773
149 457.379 02DEC2002:08:11:19 424.241
11.7887
150 475.544 02DEC2002:08:11:22 425.012
11.5315
The SAS System 09:27
Thursday, May 25, 2006 3292
trim
ohecode price time Mean
trimStd
151 437.047 02DEC2002:08:11:23 425.023
11.6818
152 464.653 02DEC2002:08:11:24 424.761
11.8525
153 456.969 02DEC2002:08:11:25 424.557
11.4575
154 450.776 02DEC2002:08:11:25 424.850
11.4188
155 422.227 02DEC2002:08:11:25 425.147
11.3779
156 448.280 02DEC2002:08:11:27 425.881
11.9257
157 421.413 02DEC2002:08:11:28 426.053
12.0634
158 416.500 02DEC2002:08:11:29 426.472
11.8430
159 439.600 02DEC2002:08:11:30 426.964
11.6569
160 425.000 02DEC2002:08:11:33 428.300
11.3296
161 468.524 02DEC2002:08:11:34 428.728
11.5448
162 430.839 02DEC2002:08:11:34 429.094
11.5034
163 413.227 02DEC2002:08:11:36 429.764
11.8785
164 425.094 02DEC2002:08:11:36 429.764
11.8785
165 432.260 02DEC2002:08:11:38 430.424
11.9366
166 417.815 02DEC2002:08:11:39 430.424
11.9366
167 463.576 02DEC2002:08:11:39 430.424
11.9366
168 434.242 02DEC2002:08:11:39 430.424
11.9366
169 425.144 02DEC2002:08:11:40 430.448
11.8577
170 431.714 02DEC2002:08:11:42 430.571
11.7243
171 422.998 02DEC2002:08:11:42 430.571
11.7243
172 418.246 02DEC2002:08:11:42 430.571
11.7243
173 448.668 02DEC2002:08:11:42 430.571
11.7243
174 428.542 02DEC2002:08:11:43 430.571
11.7243
175 416.814 02DEC2002:08:11:43 430.571
11.7243
176 416.589 02DEC2002:08:11:45 429.685
11.5222
177 420.889 02DEC2002:08:11:46 429.685
11.5222
178 424.667 02DEC2002:08:11:46 429.685
11.5222
179 411.073 02DEC2002:08:11:47 429.070
10.9380
180 433.805 02DEC2002:08:11:49 429.358
11.0520
181 425.573 02DEC2002:08:11:49 428.589
10.2002
182 416.000 02DEC2002:08:11:50 428.589
10.2002
183 431.442 02DEC2002:08:11:50 428.589
10.2002
184 416.000 02DEC2002:08:11:51 428.589
10.2002
185 433.386 02DEC2002:08:11:52 428.589
10.2002
186 416.948 02DEC2002:08:11:54 426.893
8.6191
187 430.805 02DEC2002:08:11:58 425.025
6.6971
188 434.978 02DEC2002:08:12:00 425.350
6.6684
189 415.500 02DEC2002:08:12:01 424.912
6.3773
190 428.952 02DEC2002:08:12:02 424.580
6.4647
191 420.581 02DEC2002:08:12:11 422.705
6.4602
192 420.555 02DEC2002:08:12:14 421.692
6.6929
193 418.112 02DEC2002:08:12:27 412.298
12.0268
194 403.281 02DEC2002:08:12:31 406.132
10.4259
195 400.554 02DEC2002:08:12:35 404.358
9.6280
196 399.541 02DEC2002:08:12:45 399.637
5.0566
197 400.860 02DEC2002:08:12:48 399.637
5.0566
198 397.712 02DEC2002:08:12:49 399.637
5.0566
199 389.273 02DEC2002:08:12:54 399.637
5.0566
200 394.252 02DEC2002:08:12:58 398.337
3.6650
J S Huang
1-515-557-3987
fax 1-515-557-2422
>>> "Noel, Dorian" <D.Noel@icmacentre.reading.ac.uk> 05/25/06 2:44 PM
>>>
Yes, the resulting dataset should contain the original variables
(ohecode, time), as well as trimMean and trimStd (newly created
variables). The problem with your code is that the original variables
are not retained in the resulting dataset.
-----Original Message-----
From: Jiann-Shiun Huang [mailto:Jiann-Shiun.Huang@amerus.com]
Sent: 25 May 2006 19:47
To: Noel, Dorian
Subject: result
Dorian:
Is it correct to assume the resulting dataset has the following
variables:
ohecode time price trimMean and trimStd,
where trimMean and trimStd are newly calculated trimmed mean and
trimmed standard deviation, respectively?
J S Huang
1-515-557-3987
fax 1-515-557-2422
