SAS Viyaでグルーピングした結果を別テーブルに保存する

[SAS ViyaはAIプラットフォーム](https://www.sas.com/ja_jp/software/viya.html)です。大量のデータを素早く分析したり、機械学習を用いて新しいデータの分類や判別をすることができます。開発者であればJupyter Notebookを使ってデモ環境で試すことができます。

今回はデータの簡易的な分類分けを行い、その結果をテーブルに保存する方法を紹介します。

## 元データについて

元データは[GitHubにて公開しているcars.csv](https://raw.githubusercontent.com/sassoftware/sas-viya-programming/master/data/cars.csv)です。

まずデータをグルーピングします。

```py
cars = conn.CASTable('cars')
cars.groupby=['Origin']
cars.where = 'MSRP < 100000 and MPG_City < 40'
nomList = ['Type','DriveTrain']
contList = ['MPG_City','Weight','Length']
```

そして、そのデータの一般線形化モデル（GLM）を取得します。

```py
linear4 = cars.glm
```

## 出力先の定義と設定

そして、この線形モデルの出力先を設定します。

```py
linear4.output.casout = conn.CASTable('MSRPPredictionGroupBy')
```

後はデータを設定し、最後にlinear4を実行して終了です。

```py
linear4.target = 'MSRP'
linear4.inputs = nomList + contList
linear4.nominals = nomList
linear4.display.names = ['FitStatistics','ParameterEstimates']
linear4.output.copyVars = 'ALL';
linear4.output.pred = 'Predicted_MSRP'
linear4.output.resid = 'Residual_MSRP'
linear4.output.lcl = 'LCL_MSRP'
linear4.output.ucl = 'UCL_MSRP'
linear4()
```

## 出力

結果は以下のように出てきます。

<div class="cas-results-key"><b>&#167; ByGroupInfo</b></div>
<div class="cas-results-body">
<div>
<table border="1" class="dataframe"><caption>BY Groups</caption>
  <thead>
    <tr style="text-align: right;">
      <th title=""></th>
      <th title="Origin">Origin</th>
      <th title="Origin_f">Origin_f</th>
      <th title="_key_">_key_</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>Asia</td>
      <td>Asia</td>
      <td>Asia</td>
    </tr>
    <tr>
      <th>1</th>
      <td>Europe</td>
      <td>Europe</td>
      <td>Europe</td>
    </tr>
    <tr>
      <th>2</th>
      <td>USA</td>
      <td>USA</td>
      <td>USA</td>
    </tr>
  </tbody>
</table>
</div>
</div>
<div class="cas-results-key"><hr/><b>&#167; ByGroup1.FitStatistics</b></div>
<div class="cas-results-body">
<div>
<table border="1" class="dataframe"><caption>Fit Statistics</caption>
  <thead>
    <tr style="text-align: right;">
      <th title=""></th>
      <th title="RowId">RowId</th>
      <th title="Description">Description</th>
      <th title="Value">Value</th>
    </tr>
    <tr>
      <th title="Origin">Origin</th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Asia</th>
      <td>RMSE</td>
      <td>Root MSE</td>
      <td>7.023321e+03</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>RSQUARE</td>
      <td>R-Square</td>
      <td>6.432711e-01</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>ADJRSQ</td>
      <td>Adj R-Sq</td>
      <td>6.211293e-01</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>AIC</td>
      <td>AIC</td>
      <td>2.912330e+03</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>AICC</td>
      <td>AICC</td>
      <td>2.914176e+03</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>SBC</td>
      <td>SBC</td>
      <td>2.785764e+03</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>TRAIN_ASE</td>
      <td>ASE</td>
      <td>4.614465e+07</td>
    </tr>
  </tbody>
</table>
</div>
</div>
<div class="cas-results-key"><hr/><b>&#167; ByGroup1.ParameterEstimates</b></div>
<div class="cas-results-body">
<div>
<table border="1" class="dataframe"><caption>Parameter Estimates</caption>
  <thead>
    <tr style="text-align: right;">
      <th title=""></th>
      <th title="Effect">Effect</th>
      <th title="Type">Type</th>
      <th title="DriveTrain">DriveTrain</th>
      <th title="Parameter">Parameter</th>
      <th title="DF">DF</th>
      <th title="Estimate">Estimate</th>
      <th title="StdErr">StdErr</th>
      <th title="tValue">tValue</th>
      <th title="Probt">Probt</th>
    </tr>
    <tr>
      <th title="Origin">Origin</th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Asia</th>
      <td>Intercept</td>
      <td></td>
      <td></td>
      <td>Intercept</td>
      <td>1</td>
      <td>-7800.227372</td>
      <td>19135.597840</td>
      <td>-0.407629</td>
      <td>6.841472e-01</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>Type</td>
      <td>SUV</td>
      <td></td>
      <td>Type SUV</td>
      <td>1</td>
      <td>-968.795973</td>
      <td>2805.772482</td>
      <td>-0.345287</td>
      <td>7.303792e-01</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>Type</td>
      <td>Sedan</td>
      <td></td>
      <td>Type Sedan</td>
      <td>1</td>
      <td>3766.422921</td>
      <td>2436.960318</td>
      <td>1.545541</td>
      <td>1.243951e-01</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>Type</td>
      <td>Sports</td>
      <td></td>
      <td>Type Sports</td>
      <td>1</td>
      <td>9154.062848</td>
      <td>2929.463572</td>
      <td>3.124826</td>
      <td>2.149295e-03</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>Type</td>
      <td>Truck</td>
      <td></td>
      <td>Type Truck</td>
      <td>1</td>
      <td>-11550.233853</td>
      <td>3982.945107</td>
      <td>-2.899923</td>
      <td>4.313916e-03</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>Type</td>
      <td>Wagon</td>
      <td></td>
      <td>Type Wagon</td>
      <td>0</td>
      <td>0.000000</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>DriveTrain</td>
      <td></td>
      <td>All</td>
      <td>DriveTrain All</td>
      <td>1</td>
      <td>-8003.325899</td>
      <td>2150.711618</td>
      <td>-3.721245</td>
      <td>2.828697e-04</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>DriveTrain</td>
      <td></td>
      <td>Front</td>
      <td>DriveTrain Front</td>
      <td>1</td>
      <td>-12340.999993</td>
      <td>1974.140868</td>
      <td>-6.251327</td>
      <td>4.295785e-09</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>DriveTrain</td>
      <td></td>
      <td>Rear</td>
      <td>DriveTrain Rear</td>
      <td>0</td>
      <td>0.000000</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>MPG_City</td>
      <td></td>
      <td></td>
      <td>MPG_City</td>
      <td>1</td>
      <td>17.219012</td>
      <td>250.187094</td>
      <td>0.068825</td>
      <td>9.452241e-01</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>Weight</td>
      <td></td>
      <td></td>
      <td>Weight</td>
      <td>1</td>
      <td>10.934945</td>
      <td>2.354866</td>
      <td>4.643552</td>
      <td>7.607689e-06</td>
    </tr>
    <tr>
      <th>Asia</th>
      <td>Length</td>
      <td></td>
      <td></td>
      <td>Length</td>
      <td>1</td>
      <td>14.517900</td>
      <td>114.818053</td>
      <td>0.126443</td>
      <td>8.995567e-01</td>
    </tr>
  </tbody>
</table>
</div>
</div>
<div class="cas-results-key"><hr/><b>&#167; ByGroup2.FitStatistics</b></div>
<div class="cas-results-body">
<div>
<table border="1" class="dataframe"><caption>Fit Statistics</caption>
  <thead>
    <tr style="text-align: right;">
      <th title=""></th>
      <th title="RowId">RowId</th>
      <th title="Description">Description</th>
      <th title="Value">Value</th>
    </tr>
    <tr>
      <th title="Origin">Origin</th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Europe</th>
      <td>RMSE</td>
      <td>Root MSE</td>
      <td>8.984900e+03</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>RSQUARE</td>
      <td>R-Square</td>
      <td>7.682232e-01</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>ADJRSQ</td>
      <td>Adj R-Sq</td>
      <td>7.513667e-01</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>AIC</td>
      <td>AIC</td>
      <td>2.296227e+03</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>AICC</td>
      <td>AICC</td>
      <td>2.298264e+03</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>SBC</td>
      <td>SBC</td>
      <td>2.200239e+03</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>TRAIN_ASE</td>
      <td>ASE</td>
      <td>7.462291e+07</td>
    </tr>
  </tbody>
</table>
</div>
</div>
<div class="cas-results-key"><hr/><b>&#167; ByGroup2.ParameterEstimates</b></div>
<div class="cas-results-body">
<div>
<table border="1" class="dataframe"><caption>Parameter Estimates</caption>
  <thead>
    <tr style="text-align: right;">
      <th title=""></th>
      <th title="Effect">Effect</th>
      <th title="Type">Type</th>
      <th title="DriveTrain">DriveTrain</th>
      <th title="Parameter">Parameter</th>
      <th title="DF">DF</th>
      <th title="Estimate">Estimate</th>
      <th title="StdErr">StdErr</th>
      <th title="tValue">tValue</th>
      <th title="Probt">Probt</th>
    </tr>
    <tr>
      <th title="Origin">Origin</th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Europe</th>
      <td>Intercept</td>
      <td></td>
      <td></td>
      <td>Intercept</td>
      <td>1</td>
      <td>-97268.426796</td>
      <td>23984.147398</td>
      <td>-4.055530</td>
      <td>9.371452e-05</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>Type</td>
      <td>SUV</td>
      <td></td>
      <td>Type SUV</td>
      <td>1</td>
      <td>-8680.048584</td>
      <td>4832.887423</td>
      <td>-1.796038</td>
      <td>7.523358e-02</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>Type</td>
      <td>Sedan</td>
      <td></td>
      <td>Type Sedan</td>
      <td>1</td>
      <td>3983.432570</td>
      <td>2826.381315</td>
      <td>1.409375</td>
      <td>1.615450e-01</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>Type</td>
      <td>Sports</td>
      <td></td>
      <td>Type Sports</td>
      <td>1</td>
      <td>28690.137970</td>
      <td>3766.149186</td>
      <td>7.617897</td>
      <td>9.639114e-12</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>Type</td>
      <td>Wagon</td>
      <td></td>
      <td>Type Wagon</td>
      <td>0</td>
      <td>0.000000</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>DriveTrain</td>
      <td></td>
      <td>All</td>
      <td>DriveTrain All</td>
      <td>1</td>
      <td>-5872.661455</td>
      <td>2354.280432</td>
      <td>-2.494461</td>
      <td>1.410149e-02</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>DriveTrain</td>
      <td></td>
      <td>Front</td>
      <td>DriveTrain Front</td>
      <td>1</td>
      <td>-5977.536993</td>
      <td>2282.743859</td>
      <td>-2.618575</td>
      <td>1.007440e-02</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>DriveTrain</td>
      <td></td>
      <td>Rear</td>
      <td>DriveTrain Rear</td>
      <td>0</td>
      <td>0.000000</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>MPG_City</td>
      <td></td>
      <td></td>
      <td>MPG_City</td>
      <td>1</td>
      <td>-572.454386</td>
      <td>438.201671</td>
      <td>-1.306372</td>
      <td>1.941510e-01</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>Weight</td>
      <td></td>
      <td></td>
      <td>Weight</td>
      <td>1</td>
      <td>15.461755</td>
      <td>3.107484</td>
      <td>4.975652</td>
      <td>2.418969e-06</td>
    </tr>
    <tr>
      <th>Europe</th>
      <td>Length</td>
      <td></td>
      <td></td>
      <td>Length</td>
      <td>1</td>
      <td>514.342947</td>
      <td>125.888841</td>
      <td>4.085691</td>
      <td>8.374752e-05</td>
    </tr>
  </tbody>
</table>
</div>
</div>
<div class="cas-results-key"><hr/><b>&#167; ByGroup3.FitStatistics</b></div>
<div class="cas-results-body">
<div>
<table border="1" class="dataframe"><caption>Fit Statistics</caption>
  <thead>
    <tr style="text-align: right;">
      <th title=""></th>
      <th title="RowId">RowId</th>
      <th title="Description">Description</th>
      <th title="Value">Value</th>
    </tr>
    <tr>
      <th title="Origin">Origin</th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>USA</th>
      <td>RMSE</td>
      <td>Root MSE</td>
      <td>7.336632e+03</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>RSQUARE</td>
      <td>R-Square</td>
      <td>6.317860e-01</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>ADJRSQ</td>
      <td>Adj R-Sq</td>
      <td>6.075967e-01</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>AIC</td>
      <td>AIC</td>
      <td>2.775430e+03</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>AICC</td>
      <td>AICC</td>
      <td>2.777386e+03</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>SBC</td>
      <td>SBC</td>
      <td>2.656335e+03</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>TRAIN_ASE</td>
      <td>ASE</td>
      <td>5.016452e+07</td>
    </tr>
  </tbody>
</table>
</div>
</div>
<div class="cas-results-key"><hr/><b>&#167; ByGroup3.ParameterEstimates</b></div>
<div class="cas-results-body">
<div>
<table border="1" class="dataframe"><caption>Parameter Estimates</caption>
  <thead>
    <tr style="text-align: right;">
      <th title=""></th>
      <th title="Effect">Effect</th>
      <th title="Type">Type</th>
      <th title="DriveTrain">DriveTrain</th>
      <th title="Parameter">Parameter</th>
      <th title="DF">DF</th>
      <th title="Estimate">Estimate</th>
      <th title="StdErr">StdErr</th>
      <th title="tValue">tValue</th>
      <th title="Probt">Probt</th>
    </tr>
    <tr>
      <th title="Origin">Origin</th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
      <th title=""></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>USA</th>
      <td>Intercept</td>
      <td></td>
      <td></td>
      <td>Intercept</td>
      <td>1</td>
      <td>27189.194732</td>
      <td>17038.936722</td>
      <td>1.595710</td>
      <td>0.112858</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>Type</td>
      <td>SUV</td>
      <td></td>
      <td>Type SUV</td>
      <td>1</td>
      <td>-2642.099747</td>
      <td>3692.428467</td>
      <td>-0.715545</td>
      <td>0.475490</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>Type</td>
      <td>Sedan</td>
      <td></td>
      <td>Type Sedan</td>
      <td>1</td>
      <td>1143.294987</td>
      <td>2930.398473</td>
      <td>0.390150</td>
      <td>0.697032</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>Type</td>
      <td>Sports</td>
      <td></td>
      <td>Type Sports</td>
      <td>1</td>
      <td>16249.501038</td>
      <td>4286.648839</td>
      <td>3.790724</td>
      <td>0.000224</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>Type</td>
      <td>Truck</td>
      <td></td>
      <td>Type Truck</td>
      <td>1</td>
      <td>-8503.479255</td>
      <td>3650.165897</td>
      <td>-2.329614</td>
      <td>0.021288</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>Type</td>
      <td>Wagon</td>
      <td></td>
      <td>Type Wagon</td>
      <td>0</td>
      <td>0.000000</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>DriveTrain</td>
      <td></td>
      <td>All</td>
      <td>DriveTrain All</td>
      <td>1</td>
      <td>-2882.556364</td>
      <td>2448.067975</td>
      <td>-1.177482</td>
      <td>0.241045</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>DriveTrain</td>
      <td></td>
      <td>Front</td>
      <td>DriveTrain Front</td>
      <td>1</td>
      <td>-660.205840</td>
      <td>1827.536082</td>
      <td>-0.361255</td>
      <td>0.718466</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>DriveTrain</td>
      <td></td>
      <td>Rear</td>
      <td>DriveTrain Rear</td>
      <td>0</td>
      <td>0.000000</td>
      <td>NaN</td>
      <td>NaN</td>
      <td>NaN</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>MPG_City</td>
      <td></td>
      <td></td>
      <td>MPG_City</td>
      <td>1</td>
      <td>-1069.867684</td>
      <td>340.679897</td>
      <td>-3.140390</td>
      <td>0.002067</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>Weight</td>
      <td></td>
      <td></td>
      <td>Weight</td>
      <td>1</td>
      <td>6.966966</td>
      <td>1.889464</td>
      <td>3.687271</td>
      <td>0.000326</td>
    </tr>
    <tr>
      <th>USA</th>
      <td>Length</td>
      <td></td>
      <td></td>
      <td>Length</td>
      <td>1</td>
      <td>-21.416004</td>
      <td>71.698994</td>
      <td>-0.298693</td>
      <td>0.765627</td>
    </tr>
  </tbody>
</table>
</div>
</div>
<div class="cas-output-area"></div>
<p class="cas-results-performance"><small><span class="cas-elapsed">elapsed 0.00926s</span> &#183; <span class="cas-user">user 0.008s</span> &#183; <span class="cas-sys">sys 0.006s</span> &#183; <span class="cas-memory">mem 24.9MB</span></small></p>

## まとめ

出力先を指定しておくことで、重たい分析処理であってもすぐに結果を何度も確認できます。SQLで書くこともできますが、大抵行わないでしょう。Pythonでのプログラミングであれば出力先のテーブル名を指定するだけなので簡単です。ぜひお試しください。

[SAS for Developers | SAS](https://developer.sas.com/home.html)