SQLプログラマーのかたに質問 

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行目だけをとって、

3+1 割 3+1 としたいです。

 

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コード内で簡単にできそうな方法を探しています。

 

ありがとうございます。

 

英語喉オンラインサロン・コースへ飛ぶ

3 thoughts on “SQLプログラマーのかたに質問 ”

  1. tadaと申します。以前skypeレッスンして頂きました。
    SQLですが、一発では無理で、クエリが5個必要だと思います。
    質問の意図と違ったらお許しを。
    で、具体的には、以下のようになると思っています。
    Accessで動くSQL文です。

    クエリ1個目
    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;

    クエリ2個目
    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;

    クエリ3個目
    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;

    クエリ4個目
    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;

    クエリ5個目
    SELECT
    team, Sum(ss1) as 1系, sum(ss2) as 2系, sum(ss3) as 3系
    FROM q1q2v3
    GROUP BY team;

  2. ありがとうございます!

    一瞬、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;

    お時間ありがとうございました。

  3. こんなふうに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;

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.