Date: Tue, 20 Jun 2006 09:06:11 -0700
Reply-To: "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Subject: Re: HTML proc report by quarters and years
In-Reply-To: A<1150817230.684795.202990@r2g2000cwb.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"
Hi,
Here is one of several ways:
data sample;
input CNDTFPAY datetime. PER 11.3;
format CNDTFPAY datetime.;
cards;
24JAN2000:00:00:00 103.160
25JAN2000:00:00:00 101.972
25JAN2000:00:00:00 119.967
26JAN2000:00:00:00 100.000
28JAN2000:00:00:00 114.322
28JAN2000:00:00:00 101.083
02FEB2000:00:00:00 116.111
02FEB2000:00:00:00 99.338
02FEB2000:00:00:00 98.018
04FEB2000:00:00:00 103.965
04FEB2000:00:00:00 100.000
04FEB2000:00:00:00 130.619
04FEB2000:00:00:00 90.484
10FEB2000:00:00:00 90.865
10FEB2000:00:00:00 110.221
10FEB2000:00:00:00 98.988
10FEB2000:00:00:00 99.565
10FEB2000:00:00:00 145.515
10FEB2000:00:00:00 102.241
11FEB2000:00:00:00 98.875
11FEB2000:00:00:00 100.041
11FEB2000:00:00:00 102.806
11FEB2000:00:00:00 105.507
11FEB2000:00:00:00 100.000
11FEB2000:00:00:00 90.603
11FEB2000:00:00:00 76.554
11FEB2000:00:00:00 102.528
11FEB2000:00:00:00 105.263
11FEB2000:00:00:00 90.682
11FEB2000:00:00:00 154.633
11FEB2000:00:00:00 95.047
11FEB2000:00:00:00 95.072
11FEB2000:00:00:00 92.810
11FEB2000:00:00:00 97.780
14FEB2000:00:00:00 98.924
14FEB2000:00:00:00 98.037
14FEB2000:00:00:00 94.614
14FEB2000:00:00:00 96.731
15FEB2000:00:00:00 99.570
15FEB2000:00:00:00 112.302
15FEB2000:00:00:00 102.603
15FEB2000:00:00:00 97.037
15FEB2000:00:00:00 123.738
15FEB2000:00:00:00 126.416
15FEB2000:00:00:00 131.711
15FEB2000:00:00:00 97.567
15FEB2000:00:00:00 94.695
16FEB2000:00:00:00 100.125
18FEB2000:00:00:00 109.947
18FEB2000:00:00:00 97.783
18FEB2000:00:00:00 132.587
18FEB2000:00:00:00 92.856
18FEB2000:00:00:00 98.639
18FEB2000:00:00:00 87.491
18FEB2000:00:00:00 78.767
18FEB2000:00:00:00 98.803
18FEB2000:00:00:00 119.458
22FEB2000:00:00:00 99.534
22FEB2000:00:00:00 107.727
22FEB2000:00:00:00 107.458
22FEB2000:00:00:00 168.465
22FEB2000:00:00:00 69.937
23FEB2000:00:00:00 92.453
23FEB2000:00:00:00 106.044
23FEB2000:00:00:00 100.366
24FEB2000:00:00:00 96.357
01MAR2000:00:00:00 110.337
01MAR2000:00:00:00 197.503
01MAR2000:00:00:00 94.662
01MAR2000:00:00:00 100.000
01MAR2000:00:00:00 95.877
01MAR2000:00:00:00 103.655
06MAR2000:00:00:00 100.921
06MAR2000:00:00:00 101.738
06MAR2000:00:00:00 90.139
06MAR2000:00:00:00 107.888
06MAR2000:00:00:00 87.543
08MAR2000:00:00:00 91.291
08MAR2000:00:00:00 95.560
08MAR2000:00:00:00 94.502
08MAR2000:00:00:00 94.012
08MAR2000:00:00:00 89.678
08MAR2000:00:00:00 97.559
08MAR2000:00:00:00 92.595
08MAR2000:00:00:00 99.082
09MAR2000:00:00:00 151.963
09MAR2000:00:00:00 95.558
09MAR2000:00:00:00 96.414
09MAR2000:00:00:00 100.000
09MAR2000:00:00:00 97.917
09MAR2000:00:00:00 86.293
09MAR2000:00:00:00 106.258
10MAR2000:00:00:00 103.440
13MAR2000:00:00:00 92.013
13MAR2000:00:00:00 99.358
13MAR2000:00:00:00 100.480
13MAR2000:00:00:00 107.821
16MAR2000:00:00:00 113.004
16MAR2000:00:00:00 91.968
16MAR2000:00:00:00 95.360
16MAR2000:00:00:00 92.172
16MAR2000:00:00:00 106.776
16MAR2000:00:00:00 99.502
16MAR2000:00:00:00 102.042
17MAR2000:00:00:00 99.850
17MAR2000:00:00:00 101.840
17MAR2000:00:00:00 100.000
17MAR2000:00:00:00 102.409
17MAR2000:00:00:00 106.583
17MAR2000:00:00:00 100.502
17MAR2000:00:00:00 95.986
17MAR2000:00:00:00 86.391
27MAR2000:00:00:00 77.115
27MAR2000:00:00:00 123.816
27MAR2000:00:00:00 102.971
27MAR2000:00:00:00 101.273
27MAR2000:00:00:00 102.011
28MAR2000:00:00:00 96.883
29MAR2000:00:00:00 95.304
29MAR2000:00:00:00 90.964
30MAR2000:00:00:00 113.090
30MAR2000:00:00:00 166.941
30MAR2000:00:00:00 101.205
30MAR2000:00:00:00 97.107
30MAR2000:00:00:00 99.401
30MAR2000:00:00:00 108.075
30MAR2000:00:00:00 121.179
30MAR2000:00:00:00 95.215
05APR2000:00:00:00 96.095
05APR2000:00:00:00 97.600
05APR2000:00:00:00 101.224
05APR2000:00:00:00 104.543
06APR2000:00:00:00 124.883
06APR2000:00:00:00 121.217
06APR2000:00:00:00 133.170
07APR2000:00:00:00 95.002
07APR2000:00:00:00 96.908
11APR2000:00:00:00 89.040
11APR2000:00:00:00 117.575
11APR2000:00:00:00 87.012
12APR2000:00:00:00 116.407
12APR2000:00:00:00 95.845
12APR2000:00:00:00 99.504
12APR2000:00:00:00 94.924
13APR2000:00:00:00 99.964
13APR2000:00:00:00 97.345
13APR2000:00:00:00 106.774
13APR2000:00:00:00 100.658
13APR2000:00:00:00 97.168
13APR2000:00:00:00 107.676
13APR2000:00:00:00 105.231
13APR2000:00:00:00 103.731
13APR2000:00:00:00 110.342
13APR2000:00:00:00 72.382
13APR2000:00:00:00 94.560
17APR2000:00:00:00 100.015
21APR2000:00:00:00 103.955
21APR2000:00:00:00 103.928
21APR2000:00:00:00 94.837
24APR2000:00:00:00 138.381
24APR2000:00:00:00 113.461
25APR2000:00:00:00 106.775
25APR2000:00:00:00 88.211
25APR2000:00:00:00 97.568
25APR2000:00:00:00 101.517
01MAY2000:00:00:00 95.545
01MAY2000:00:00:00 88.025
01MAY2000:00:00:00 99.872
01MAY2000:00:00:00 107.755
01MAY2000:00:00:00 97.912
01MAY2000:00:00:00 138.733
03MAY2000:00:00:00 116.327
03MAY2000:00:00:00 197.868
03MAY2000:00:00:00 101.441
03MAY2000:00:00:00 117.276
03MAY2000:00:00:00 106.643
08MAY2000:00:00:00 99.048
09MAY2000:00:00:00 110.989
09MAY2000:00:00:00 97.532
09MAY2000:00:00:00 118.511
09MAY2000:00:00:00 95.578
10MAY2000:00:00:00 95.125
10MAY2000:00:00:00 103.327
10MAY2000:00:00:00 99.357
11MAY2000:00:00:00 92.978
11MAY2000:00:00:00 133.385
11MAY2000:00:00:00 102.565
15MAY2000:00:00:00 104.489
15MAY2000:00:00:00 95.194
15MAY2000:00:00:00 110.301
15MAY2000:00:00:00 80.090
15MAY2000:00:00:00 88.406
15MAY2000:00:00:00 83.776
18MAY2000:00:00:00 105.888
18MAY2000:00:00:00 109.230
19MAY2000:00:00:00 96.485
19MAY2000:00:00:00 101.123
19MAY2000:00:00:00 111.907
19MAY2000:00:00:00 98.618
19MAY2000:00:00:00 96.891
19MAY2000:00:00:00 101.309
19MAY2000:00:00:00 117.959
24MAY2000:00:00:00 104.096
24MAY2000:00:00:00 101.679
24MAY2000:00:00:00 120.195
09JUN2000:00:00:00 99.949
09JUN2000:00:00:00 81.987
12JUN2000:00:00:00 99.238
12JUN2000:00:00:00 103.540
12JUN2000:00:00:00 101.722
13JUN2000:00:00:00 106.829
13JUN2000:00:00:00 110.952
13JUN2000:00:00:00 106.487
13JUN2000:00:00:00 97.731
13JUN2000:00:00:00 92.708
13JUN2000:00:00:00 91.513
14JUN2000:00:00:00 106.128
14JUN2000:00:00:00 116.953
14JUN2000:00:00:00 77.067
14JUN2000:00:00:00 116.703
15JUN2000:00:00:00 100.000
15JUN2000:00:00:00 98.924
19JUN2000:00:00:00 106.447
26JUN2000:00:00:00 109.739
26JUN2000:00:00:00 98.806
27JUN2000:00:00:00 98.448
27JUN2000:00:00:00 109.841
28JUN2000:00:00:00 105.917
28JUN2000:00:00:00 197.807
28JUN2000:00:00:00 102.203
29JUN2000:00:00:00 96.553
29JUN2000:00:00:00 98.256
29JUN2000:00:00:00 103.808
;
run;
proc sql;
create table result as
select
a.*,
b.PER_yr_ge_105
from
(
select datepart(intnx('dtyear',CNDTFPAY,0,'e'))
as year format=year4.,
datepart(intnx('dtqtr',CNDTFPAY,0,'e'))
as quarter format=yyq6.,
sum(PER) as PER_qtr_ge_105
from sample
where PER ge 105
group by year, quarter
) as a
left join
(
select datepart(intnx('dtyear',CNDTFPAY,0,'e'))
as year format=year4.,
sum(PER) as PER_yr_ge_105
from sample
where PER ge 105
group by year
) as b
on
a.year eq b.year
;
quit;
ods html file='c:\temp\abc.htm';
proc print data=result;
run;
ods html close;
Hope this is helpful.
Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group
Russell
Global Leaders in Multi-Manager Investing
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Greg Curson
Sent: Tuesday, June 20, 2006 8:27 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: HTML proc report by quarters and years
I am not very good at html and using different functions of SAS
I need to produce a calendar year quarterly report for data for the last
5 years and produce the report in HTML.
I am needing the percent of obs where PER is >= 105 for each calendar
quarter then a cumulative for each quarter within the year and then a
total for each year for the last five years worth of data, I am showing
a sample dataset below. Can someone help me?
thanks!
Obs CNDTFPAY PER
2138 24JAN2000:00:00:00 103.160
2139 25JAN2000:00:00:00 101.972
2140 25JAN2000:00:00:00 119.967
2141 26JAN2000:00:00:00 100.000
2142 28JAN2000:00:00:00 114.322
2143 28JAN2000:00:00:00 101.083
2144 02FEB2000:00:00:00 116.111
2145 02FEB2000:00:00:00 99.338
2146 02FEB2000:00:00:00 98.018
2147 04FEB2000:00:00:00 103.965
2148 04FEB2000:00:00:00 100.000
2149 04FEB2000:00:00:00 130.619
2150 04FEB2000:00:00:00 90.484
2151 10FEB2000:00:00:00 90.865
2152 10FEB2000:00:00:00 110.221
2153 10FEB2000:00:00:00 98.988
2154 10FEB2000:00:00:00 99.565
2155 10FEB2000:00:00:00 145.515
2156 10FEB2000:00:00:00 102.241
2157 11FEB2000:00:00:00 98.875
2158 11FEB2000:00:00:00 100.041
2159 11FEB2000:00:00:00 102.806
2160 11FEB2000:00:00:00 105.507
2161 11FEB2000:00:00:00 100.000
2162 11FEB2000:00:00:00 90.603
2163 11FEB2000:00:00:00 76.554
2164 11FEB2000:00:00:00 102.528
2165 11FEB2000:00:00:00 105.263
2166 11FEB2000:00:00:00 90.682
2167 11FEB2000:00:00:00 154.633
2168 11FEB2000:00:00:00 95.047
2169 11FEB2000:00:00:00 95.072
2170 11FEB2000:00:00:00 92.810
2171 11FEB2000:00:00:00 97.780
2172 14FEB2000:00:00:00 98.924
2173 14FEB2000:00:00:00 98.037
2174 14FEB2000:00:00:00 94.614
2175 14FEB2000:00:00:00 96.731
2176 15FEB2000:00:00:00 99.570
2177 15FEB2000:00:00:00 112.302
2178 15FEB2000:00:00:00 102.603
2179 15FEB2000:00:00:00 97.037
2180 15FEB2000:00:00:00 123.738
2181 15FEB2000:00:00:00 126.416
2182 15FEB2000:00:00:00 131.711
2183 15FEB2000:00:00:00 97.567
2184 15FEB2000:00:00:00 94.695
2185 16FEB2000:00:00:00 100.125
2186 18FEB2000:00:00:00 109.947
2187 18FEB2000:00:00:00 97.783
2188 18FEB2000:00:00:00 132.587
2189 18FEB2000:00:00:00 92.856
2190 18FEB2000:00:00:00 98.639
2191 18FEB2000:00:00:00 87.491
2192 18FEB2000:00:00:00 78.767
2193 18FEB2000:00:00:00 98.803
2194 18FEB2000:00:00:00 119.458
2195 22FEB2000:00:00:00 99.534
2196 22FEB2000:00:00:00 107.727
2197 22FEB2000:00:00:00 107.458
2198 22FEB2000:00:00:00 168.465
2199 22FEB2000:00:00:00 69.937
2200 23FEB2000:00:00:00 92.453
2201 23FEB2000:00:00:00 106.044
2202 23FEB2000:00:00:00 100.366
2203 24FEB2000:00:00:00 96.357
2204 01MAR2000:00:00:00 110.337
2205 01MAR2000:00:00:00 197.503
2206 01MAR2000:00:00:00 94.662
2207 01MAR2000:00:00:00 100.000
2208 01MAR2000:00:00:00 95.877
2209 01MAR2000:00:00:00 103.655
2210 06MAR2000:00:00:00 100.921
2211 06MAR2000:00:00:00 101.738
2212 06MAR2000:00:00:00 90.139
2213 06MAR2000:00:00:00 107.888
2214 06MAR2000:00:00:00 87.543
2215 08MAR2000:00:00:00 91.291
2216 08MAR2000:00:00:00 95.560
2217 08MAR2000:00:00:00 94.502
2218 08MAR2000:00:00:00 94.012
2219 08MAR2000:00:00:00 89.678
2220 08MAR2000:00:00:00 97.559
2221 08MAR2000:00:00:00 92.595
2222 08MAR2000:00:00:00 99.082
2223 09MAR2000:00:00:00 151.963
2224 09MAR2000:00:00:00 95.558
2225 09MAR2000:00:00:00 96.414
2226 09MAR2000:00:00:00 100.000
2227 09MAR2000:00:00:00 97.917
2228 09MAR2000:00:00:00 86.293
2229 09MAR2000:00:00:00 106.258
2230 10MAR2000:00:00:00 103.440
2231 13MAR2000:00:00:00 92.013
2232 13MAR2000:00:00:00 99.358
2233 13MAR2000:00:00:00 100.480
2234 13MAR2000:00:00:00 107.821
2235 16MAR2000:00:00:00 113.004
2236 16MAR2000:00:00:00 91.968
2237 16MAR2000:00:00:00 95.360
2238 16MAR2000:00:00:00 92.172
2239 16MAR2000:00:00:00 106.776
2240 16MAR2000:00:00:00 99.502
2241 16MAR2000:00:00:00 102.042
2242 17MAR2000:00:00:00 99.850
2243 17MAR2000:00:00:00 101.840
2244 17MAR2000:00:00:00 100.000
2245 17MAR2000:00:00:00 102.409
2246 17MAR2000:00:00:00 106.583
2247 17MAR2000:00:00:00 100.502
2248 17MAR2000:00:00:00 95.986
2249 17MAR2000:00:00:00 86.391
2250 27MAR2000:00:00:00 77.115
2251 27MAR2000:00:00:00 123.816
2252 27MAR2000:00:00:00 102.971
2253 27MAR2000:00:00:00 101.273
2254 27MAR2000:00:00:00 102.011
2255 28MAR2000:00:00:00 96.883
2256 29MAR2000:00:00:00 95.304
2257 29MAR2000:00:00:00 90.964
2258 30MAR2000:00:00:00 113.090
2259 30MAR2000:00:00:00 166.941
2260 30MAR2000:00:00:00 101.205
2261 30MAR2000:00:00:00 97.107
2262 30MAR2000:00:00:00 99.401
2263 30MAR2000:00:00:00 108.075
2264 30MAR2000:00:00:00 121.179
2265 30MAR2000:00:00:00 95.215
2266 05APR2000:00:00:00 96.095
2267 05APR2000:00:00:00 97.600
2268 05APR2000:00:00:00 101.224
2269 05APR2000:00:00:00 104.543
2270 06APR2000:00:00:00 124.883
2271 06APR2000:00:00:00 121.217
2272 06APR2000:00:00:00 133.170
2273 07APR2000:00:00:00 95.002
2274 07APR2000:00:00:00 96.908
2275 11APR2000:00:00:00 89.040
2276 11APR2000:00:00:00 117.575
2277 11APR2000:00:00:00 87.012
2278 12APR2000:00:00:00 116.407
2279 12APR2000:00:00:00 95.845
2280 12APR2000:00:00:00 99.504
2281 12APR2000:00:00:00 94.924
2282 13APR2000:00:00:00 99.964
2283 13APR2000:00:00:00 97.345
2284 13APR2000:00:00:00 106.774
2285 13APR2000:00:00:00 100.658
2286 13APR2000:00:00:00 97.168
2287 13APR2000:00:00:00 107.676
2288 13APR2000:00:00:00 105.231
2289 13APR2000:00:00:00 103.731
2290 13APR2000:00:00:00 110.342
2291 13APR2000:00:00:00 72.382
2292 13APR2000:00:00:00 94.560
2293 17APR2000:00:00:00 100.015
2294 21APR2000:00:00:00 103.955
2295 21APR2000:00:00:00 103.928
2296 21APR2000:00:00:00 94.837
2297 24APR2000:00:00:00 138.381
2298 24APR2000:00:00:00 113.461
2299 25APR2000:00:00:00 106.775
2300 25APR2000:00:00:00 88.211
2301 25APR2000:00:00:00 97.568
2302 25APR2000:00:00:00 101.517
2303 01MAY2000:00:00:00 95.545
2304 01MAY2000:00:00:00 88.025
2305 01MAY2000:00:00:00 99.872
2306 01MAY2000:00:00:00 107.755
2307 01MAY2000:00:00:00 97.912
2308 01MAY2000:00:00:00 138.733
2309 03MAY2000:00:00:00 116.327
2310 03MAY2000:00:00:00 197.868
2311 03MAY2000:00:00:00 101.441
2312 03MAY2000:00:00:00 117.276
2313 03MAY2000:00:00:00 106.643
2314 08MAY2000:00:00:00 99.048
2315 09MAY2000:00:00:00 110.989
2316 09MAY2000:00:00:00 97.532
2317 09MAY2000:00:00:00 118.511
2318 09MAY2000:00:00:00 95.578
2319 10MAY2000:00:00:00 95.125
2320 10MAY2000:00:00:00 103.327
2321 10MAY2000:00:00:00 99.357
2322 11MAY2000:00:00:00 92.978
2323 11MAY2000:00:00:00 133.385
2324 11MAY2000:00:00:00 102.565
2325 15MAY2000:00:00:00 104.489
2326 15MAY2000:00:00:00 95.194
2327 15MAY2000:00:00:00 110.301
2328 15MAY2000:00:00:00 80.090
2329 15MAY2000:00:00:00 88.406
2330 15MAY2000:00:00:00 83.776
2331 18MAY2000:00:00:00 105.888
2332 18MAY2000:00:00:00 109.230
2333 19MAY2000:00:00:00 96.485
2334 19MAY2000:00:00:00 101.123
2335 19MAY2000:00:00:00 111.907
2336 19MAY2000:00:00:00 98.618
2337 19MAY2000:00:00:00 96.891
2338 19MAY2000:00:00:00 101.309
2339 19MAY2000:00:00:00 117.959
2340 24MAY2000:00:00:00 104.096
2341 24MAY2000:00:00:00 101.679
2342 24MAY2000:00:00:00 120.195
2343 09JUN2000:00:00:00 99.949
2344 09JUN2000:00:00:00 81.987
2345 12JUN2000:00:00:00 99.238
2346 12JUN2000:00:00:00 103.540
2347 12JUN2000:00:00:00 101.722
2348 13JUN2000:00:00:00 106.829
2349 13JUN2000:00:00:00 110.952
2350 13JUN2000:00:00:00 106.487
2351 13JUN2000:00:00:00 97.731
2352 13JUN2000:00:00:00 92.708
2353 13JUN2000:00:00:00 91.513
2354 14JUN2000:00:00:00 106.128
2355 14JUN2000:00:00:00 116.953
2356 14JUN2000:00:00:00 77.067
2357 14JUN2000:00:00:00 116.703
2358 15JUN2000:00:00:00 100.000
2359 15JUN2000:00:00:00 98.924
2360 19JUN2000:00:00:00 106.447
2361 26JUN2000:00:00:00 109.739
2362 26JUN2000:00:00:00 98.806
2363 27JUN2000:00:00:00 98.448
2364 27JUN2000:00:00:00 109.841
2365 28JUN2000:00:00:00 105.917
2366 28JUN2000:00:00:00 197.807
2367 28JUN2000:00:00:00 102.203
2368 29JUN2000:00:00:00 96.553
2369 29JUN2000:00:00:00 98.256
2370 29JUN2000:00:00:00 103.808
