Expanding PROC EXPAND

shiyiming

From LCChien's blog on blogspot

原文載點：<a href="http://support.sas.com/resources/papers/proceedings11/417-2011.pdf"><!-- m --><a class="postlink" href="http://support.sas.com/resources/papers/proceedings11/417-2011.pdf">http://support.sas.com/resources/papers ... 7-2011.pdf</a><!-- m --></a><br /><br />PROC EXPAND 程序是 SAS/ETS 裡面其中一個語法，提供許多跟時間相關資料的簡便處理。比方說，將日資料累加成月資料，月資料累加成季資料或年資料，也可以倒過來把季/年資料變成月資料，月資料變成日資料。若遇到缺漏值，該程序可用內插法將資料補齊。也可進行諸如moving average的資料轉換。Bruce Gilsen 在 2011 年的 SAS Global Forum 發表了一篇技術文件，介紹了兩個用 PROC EXPAND 運作的例子。<br /><br /><a name='more'></a><b>Calculate an un-weighted quarterly average from monthly data</b><br /><br />第一個例子介紹如何將月資料轉成季資料。使用的範例資料如下(部分)：<br /><pre><code>&nbsp; Obs &nbsp; &nbsp; date &nbsp; &nbsp; &nbsp; &nbsp; id &nbsp; income<br />&nbsp; &nbsp; 1 &nbsp; &nbsp;20070101 &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;1<br />&nbsp; &nbsp; 2 &nbsp; &nbsp;20070201 &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;2<br />&nbsp; &nbsp; 3 &nbsp; &nbsp;20070301 &nbsp; &nbsp;1 &nbsp; &nbsp; 15<br />&nbsp; &nbsp; 4 &nbsp; &nbsp;20070401 &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;4<br />&nbsp; &nbsp; 5 &nbsp; &nbsp;20070501 &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;5<br />&nbsp; &nbsp; 6 &nbsp; &nbsp;20070601 &nbsp; &nbsp;1 &nbsp; &nbsp; 12<br />&nbsp; &nbsp; 7 &nbsp; &nbsp;20070701 &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;7<br />&nbsp; &nbsp; 8 &nbsp; &nbsp;20070801 &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;8<br />&nbsp; &nbsp; 9 &nbsp; &nbsp;20070901 &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;9</code></pre><br />程式如下：<br /><pre><code>proc expand data=one out=two <span class="Apple-style-span" style="color: red;">from=month to=quarter</span> ;<br />&nbsp; &nbsp; &nbsp; id date;<br />&nbsp; &nbsp; &nbsp; by id;<br />&nbsp; &nbsp; &nbsp; <span class="Apple-style-span" style="color: blue;">convert income / method=aggregate observed=average</span>;<br />run;</code></pre><br />紅色部分的 from=month to=quarter 顧名思義就是把資料從月(month)轉成季(quarter)。藍色部分表示轉換的變數為 income，方法為累加(aggregate)，最後求出"非加權"平均值(average)。結果如下：<br /><pre><code>&nbsp; &nbsp;Obs &nbsp; &nbsp;id &nbsp; &nbsp; &nbsp;date &nbsp; &nbsp; income &nbsp; &nbsp;income<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(expected result)<br />&nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp;2007:1 &nbsp; &nbsp; 6.1333 &nbsp; &nbsp; &nbsp; 6<br />&nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp;2007:2 &nbsp; &nbsp; 6.9780 &nbsp; &nbsp; &nbsp; 7<br />&nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp;2007:3 &nbsp; &nbsp; 7.9891 &nbsp; &nbsp; &nbsp; 8</code></pre><br />最後一欄income(expected result)便是所求，不過程式也會把加權後的結果一併列出，即為倒數第二欄的結果。加權的方式是用天數為基準。以第三季(七～九月)結果來看，前三個月的天數分別是31, 31, 30。總計為92天，因此加權平均後的結果是<br /><pre><code>((31 * 7) + (31 * <!-- s8) --><img src="{SMILIES_PATH}/icon_cool.gif" alt="8)" title="Cool" /><!-- s8) --> + (30 * 9)) / 92 = 7.9891</code></pre><br /><b>Convert weekly data to a monthly average when the weekly values represent values for seven days</b><br /><br />先看一下範例：<br /><pre><code>&nbsp; &nbsp;Obs &nbsp; date &nbsp; &nbsp; &nbsp; id &nbsp; income<br />&nbsp; &nbsp; 1 &nbsp; &nbsp;20070530 &nbsp; &nbsp;1 &nbsp; &nbsp; 11<br />&nbsp; &nbsp; 2 &nbsp; &nbsp;20070606 &nbsp; &nbsp;1 &nbsp; &nbsp; 30<br />&nbsp; &nbsp; 3 &nbsp; &nbsp;20070613 &nbsp; &nbsp;1 &nbsp; &nbsp; 50<br />&nbsp; &nbsp; 4 &nbsp; &nbsp;20070620 &nbsp; &nbsp;1 &nbsp; &nbsp; 70<br />&nbsp; &nbsp; 5 &nbsp; &nbsp;20070627 &nbsp; &nbsp;1 &nbsp; &nbsp; 90<br />&nbsp; &nbsp; 6 &nbsp; &nbsp;20070704 &nbsp; &nbsp;1 &nbsp; &nbsp; 50<br />&nbsp; &nbsp; 7 &nbsp; &nbsp;20070711 &nbsp; &nbsp;1 &nbsp; &nbsp; 40<br />&nbsp; &nbsp; 8 &nbsp; &nbsp;20070718 &nbsp; &nbsp;1 &nbsp; &nbsp;120<br />&nbsp; &nbsp; 9 &nbsp; &nbsp;20070725 &nbsp; &nbsp;1 &nbsp; &nbsp; 80<br />&nbsp; &nbsp;10 &nbsp; &nbsp;20070801 &nbsp; &nbsp;1 &nbsp; &nbsp;100<br />&nbsp; &nbsp;11 &nbsp; &nbsp;20070808 &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;5<br />&nbsp; &nbsp;12 &nbsp; &nbsp;20070815 &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;7<br />&nbsp; &nbsp;13 &nbsp; &nbsp;20070822 &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;9<br />&nbsp; &nbsp;14 &nbsp; &nbsp;20070829 &nbsp; &nbsp;1 &nbsp; &nbsp; 11<br />&nbsp; &nbsp;15 &nbsp; &nbsp;20070905 &nbsp; &nbsp;1 &nbsp; &nbsp; 24</code></pre><br />將週資料轉成月資料的方法同第一個範例：<br /><pre><code>proc expand data=one out=two from=<span class="Apple-style-span" style="color: red;">week </span>to=<span class="Apple-style-span" style="color: red;">month </span>;<br />&nbsp; &nbsp; &nbsp; id date;<br />&nbsp; &nbsp; &nbsp; by id;<br />&nbsp; &nbsp; &nbsp; convert income / observed=average;<br />run;</code></pre><br />結果如下：<br /><pre><code>&nbsp; &nbsp;Obs &nbsp; &nbsp;id &nbsp; &nbsp; &nbsp;date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;income &nbsp; &nbsp;income<br />&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(expected result)<br />&nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp;JUN2007 &nbsp; &nbsp; &nbsp;57.093 &nbsp; &nbsp; 60<br />&nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp;JUL2007 &nbsp; &nbsp; &nbsp; 75.905 &nbsp; &nbsp; 80<br />&nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp;AUG2007 &nbsp; &nbsp; 18.852 &nbsp; &nbsp; 12</code></pre><br />只要把 from=quarter 改成 from=week 即可。但是加權平均income的數據，是用插補法先將週四至下週二的數據進行插補，然後再做平均。作者提供另一種作法：<br /><pre><code>data two;<br />&nbsp; &nbsp; &nbsp;set one;<br />&nbsp; &nbsp; &nbsp;do dailydate = date-6 to date;<br />&nbsp; &nbsp; &nbsp; &nbsp;output; &nbsp;/* transform each weekly observation to 7 daily observations */<br />&nbsp; &nbsp; &nbsp;end;<br />&nbsp; &nbsp;run;<br />proc expand data=two out=three from=day to=month ;<br />&nbsp; &nbsp; &nbsp; id dailydate;<br />&nbsp; &nbsp; &nbsp; by id;<br />&nbsp; &nbsp; &nbsp; convert income / observed=average;<br />run;</code></pre><br />這個作法是先將原始的週資料用複製的方法將沒有數字的天數補齊，如下所示：<br /><pre><code>&nbsp; &nbsp;Obs &nbsp; dailydate &nbsp; &nbsp;id &nbsp; &nbsp; income<br />&nbsp; &nbsp; 1 &nbsp; &nbsp;20070524 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 11<br />&nbsp; &nbsp; 2 &nbsp; &nbsp;20070525 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 11<br />&nbsp; &nbsp; 3 &nbsp; &nbsp;20070526 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 11<br />&nbsp; &nbsp; 4 &nbsp; &nbsp;20070527 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 11<br />&nbsp; &nbsp; 5 &nbsp; &nbsp;20070528 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 11<br />&nbsp; &nbsp; 6 &nbsp; &nbsp;20070529 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 11<br />&nbsp; &nbsp; 7 &nbsp; &nbsp;20070530 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 11<br />&nbsp; &nbsp; 8 &nbsp; &nbsp;20070531 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 30<br />&nbsp; &nbsp; 9 &nbsp; &nbsp;20070601 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 30<br />&nbsp; &nbsp;10 &nbsp; &nbsp;20070602 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 30<br />&nbsp; &nbsp;11 &nbsp; &nbsp;20070603 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 30<br />&nbsp; &nbsp;12 &nbsp; &nbsp;20070604 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 30<br />&nbsp; &nbsp;13 &nbsp; &nbsp;20070605 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 30<br />&nbsp; &nbsp;14 &nbsp; &nbsp;20070606 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 30<br />&nbsp; &nbsp;15 &nbsp; &nbsp;20070607 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 50<br />&nbsp; &nbsp;16 &nbsp; &nbsp;20070608 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 50<br />&nbsp; &nbsp;17 &nbsp; &nbsp;20070609 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 50<br />&nbsp; &nbsp;18 &nbsp; &nbsp;20070610 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 50<br />&nbsp; &nbsp;19 &nbsp; &nbsp;20070611 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 50<br />&nbsp; &nbsp;20 &nbsp; &nbsp;20070612 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 50<br />&nbsp; &nbsp;21 &nbsp; &nbsp;20070613 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 50<br />&nbsp; &nbsp;22 &nbsp; &nbsp;20070614 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 70<br />&nbsp; &nbsp;23 &nbsp; &nbsp;20070615 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 70<br />&nbsp; &nbsp;24 &nbsp; &nbsp;20070616 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 70<br />&nbsp; &nbsp;25 &nbsp; &nbsp;20070617 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; 70</code></pre><br />然後再把這個日資料換算成月資料即可。<br /><br /><b><span class="Apple-style-span" style="font-size: large;">Contact information</span></b><br />Bruce Gilsen<br />Federal Reserve Board<br />Mail Stop 157<br />Washington, DC&nbsp;20551<br />e-mail: <!-- e --><a href="mailto:bruce.gilsen@frb.gov">bruce.gilsen@frb.gov</a><!-- e --><br />phone: 202-452-2494.<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/6268919072942670865-1616952410225347325?l=sugiclub.blogspot.com' alt='' /></div>