| Team | A | B |
| A |
3 |
3 |
| A |
2 |
. |
| A |
1 |
1 |
| B |
2 |
. |
| B |
1 |
1 |
| B |
1 |
1 |
上のようなデータがありまして、以下のSQLコードを使いたいです（SAS内にあるSQLですが普通のSQLと同じだと思います）。
proc sql;
create table new_data as
select *,
sum(A)/sum(B) as result
from my_data
group by Team;
run;
しかし、このままだと、AかBに欠損値がある場合も計算に入ってしまいます。欠損値がないAとBだけに限って、sum(A)/sum(B)を出したいのですが、どうしたらよいでしょうか？
| Team | A | B |
| A |
3 |
3 |
| A |
2 |
. |
| A |
1 |
1 |
だけでいうと、sum(A)/sum(B)は
欠損値のない、1行目と3行目だけをとって、
３＋１ 割 ３＋１ としたいです。
SQLコードはあまり知らないのですが、以下のような感じにしたいです。
proc sql;
create table new_data as
select *,
sum(A)/sum(B) as result where A ne . and B ne .
from my_data
group by Team;
run;
以下で対応できるのはできるのですが、データ内のサンプル数が減ってしまいます。
proc sql;
create table new_data as
select *,
sum(A)/sum(B) as result
from my_data where A is not null and B is not null
group by Team;
run;
以下のようなデータにも対応できるようにしたいです。
| Team | A1 | B1 | A2 | B2 | A3 | B3 |
| A |
3 |
3 |
3 |
3 |
3 |
3 |
| A |
2 |
. |
2 |
. |
2 |
. |
| A |
1 |
1 |
1 |
1 |
1 |
1 |
| B |
2 |
. |
2 |
. |
2 |
. |
| B |
1 |
. |
1 |
1 |
1 |
1 |
| B |
1 |
1 |
1 |
1 |
1 |
. |
proc sql;
create table new_data as
select *,
sum(A1)/sum(B1) as result1,
sum(A2)/sum(B2) as result2,
sum(A3)/sum(B3) as result3
from my_data
group by Team;
run;
となりますが、
proc sql;
create table new_data as
select *,
sum(A1)/sum(B1) as result1, à only when A1 and B1 are not missing
sum(A2)/sum(B2) as result2, à only when A2 and B2 are not missing
sum(A3)/sum(B3) as result3à only when A2 and B2 are not missing
from my_data
group by Team;
run;
としたいです。
できればこのSQLコード内で簡単にできそうな方法を探しています。
ありがとうございます。
英語喉オンラインコースへ飛ぶ（ニュースレター登録をお願いします）
tadaと申します。以前skypeレッスンして頂きました。
SQLですが、一発では無理で、クエリが５個必要だと思います。
質問の意図と違ったらお許しを。
で、具体的には、以下のようになると思っています。
Accessで動くSQL文です。
クエリ１個目
SELECT
team, sum(a1) AS A1集計, sum(b1) AS B1集計
FROM tbl
WHERE a1 is not null
and
b1 is not null
GROUP BY team;
クエリ２個目
SELECT team, sum(a2) AS A2集計, sum(b2) AS B2集計
FROM tbl
WHERE a2 Is Not Null And b2 Is Not Null
GROUP BY team;
クエリ３個目
SELECT team, sum(a3) AS A3集計, sum(b3) AS B3集計
FROM tbl
WHERE a3 Is Not Null And b3 Is Not Null
GROUP BY team;
クエリ４個目
Select
team,A1集計 / B1集計 as ss1, 0 as ss2, 0 as ss3
From q1
UNION ALL
Select
team, 0, A2集計/B2集計,0
From q2
UNION ALL
Select
team,0, 0, A3集計/B3集計
From q3;
クエリ５個目
SELECT
team, Sum(ss1) as 1系, sum(ss2) as 2系, sum(ss3) as 3系
FROM q1q2v3
GROUP BY team;
ありがとうございます！
一瞬、FUNCTIONで解決できるかと思ったのですが、うまくいきませんでした。
ですから、このように分けるしかないのかもしれません、、、。あとCASEというのがあって、これでいけるかと思ったのですが、なぜかうまくいきません。
proc sql;
create table asdf2 as
select *,
case when age is not missing and weight is not missing then sum(age) end as result1,
case when age is not missing and weight is not missing then sum(weight) end as result2,
case when age is not missing and weight is not missing then sum(age) / sum(weight) end as result3
from asdf ;
run;
お時間ありがとうございました。
こんなふうにCASEを使うと、できるそうです。SASのサポート係さんからの返事でした。しかし、複雑すぎて、間違い探しが面倒なので、SQL以前のデータステップで処理しました。結局。
proc sql;
create table asdf2 as
select *,
case when age is not missing and weight is not missing then sum(case when age is not missing and weight is not missing then age else 0 end) end as result1,
case when age is not missing and weight is not missing then sum(case when age is not missing and weight is not missing then weight else 0 end) end as result2,
case when age is not missing and weight is not missing then calculated result1 / calculated result2 end as result3 from asdf ;
run;