SAS中的SQL :尝试通过左连接计算零值

Question

尽管对我的问题进行了彻底的研究，但我仍然无法解决我在SQL查询中遇到的问题。

我在一个客户数据库上有一个二维查询。我想按客户和产品对所有购买金额进行排序。由于并非所有客户都购买了所有产品，因此还必须显示每个客户购买为零的产品。

我有两个表:包含所有单个事务的大表purchases和第二个表products，它是通过从purchases数据库进行简单查询生成的。

该表用于列出所有可能的产品。

我的查询如下所示：

proc sql; 
create table customers_products 
as select  a.customers as customers, b.products as product,sum(a.amount) as amount format=comma16.0, count(a.amount) as transactions format=comma16.0
from products as b 
left join work.transactions as a on b.product = a.product
group by a.customers , b.products 
order by a.products, amount desc; 
quit;  

然而，没有零计数。如有任何帮助，我们不胜感激！

Answer 1

也许有一种更简单的方法可以做到这一点，但以下是我将如何解决您的问题

首先:这里有一些购买示例。每个客户有60%的机会购买一个产品1次或更多次：

data purchases;
do customer=1 to 10;
    do product=1 to 10;
        if ranuni(1) > .4 then do;
            x = ceil(4*ranuni(1)); /*Buy 1 to 4 times*/
            do i=1 to x;
                amount = ranuni(1)*10;
                output;
            end;
        end;
    end;
end;
drop i x;
run;

接下来，我获得不同的产品和客户，并获得每个客户/产品组的总数。

proc sql noprint;
create table products as
select distinct product from purchases;

create table customers as
select distinct customer from purchases;

create table totals as
select customer, product, sum(amount) as amt
from purchases
group by customer, product;
quit;

你的问题涉及到客户和产品的笛卡尔产品--即你想要所有的客户和产品。所以我继续在这里这样做。

proc sql noprint;
create table customers_x_products as 
select a.customer, b.product
from customers as a, 
     products as b;
quit;

最后，我将customers_x_products表与totals表放在一起，生成您想要的表：

proc sql noprint;
create table customers_products as
select b.customer,
       b.product,
       coalesce(a.amt,0) as amt
    from customers_x_products as b
      left join
         totals as a
      on a.product=b.product 
      and a.customer=b.customer
    order by b.customer, b.product;
quit;

Answer 2

这能解决你的问题吗？由于缺乏样本数据和所需的输出，我无法对其进行测试。

proc sql; 
create table customers_products as
select a.customers as customers
      ,b.products as product
      ,sum(a.amount) as amount format=comma16.0
      ,case
        when sum(a.amount) is missing then 0
        else count(a.amount) 
       end as transactions format=comma16.0
from products as b 
left join work.transactions as a on b.product = a.product
group by a.customers , b.products 
order by a.products, amount desc; 
quit; 

编辑:我现在看到，在您的查询中既有一个product变量，也有一个products变量。是打字错误吗？

编辑2:我制作了一些样本数据，并对其进行了查询

data products;
infile datalines;
input product $;
datalines;
a
b
c
d
;
run;
data transactions;
infile datalines;
input customers $ product $ amount;
datalines;
X a 3
X b 1
Y c 5
;
run;

proc sql; 
create table customers_products as
select a.customers as customers
            ,b.product as product
            ,sum(a.amount) as amount format=comma16.0
            ,count(a.amount) as transactions format=comma16.0
from products as b 
left join transactions as a on b.product = a.product
group by a.customers , b.product 
order by a.product, amount desc; 
quit;

这就是您的查询，除了我假设您对prodcuts和product的不同引用是错误的之外，我将它们都更改为product。这将产生以下结果：

customers product amount transactions
-------------------------------------
          d       .      0
X         a       3      1
X         b       1      1
Y         c       5      1

如您所见，我得到了一条产品b的事务计数为零的记录。因此，我不确定我是否理解您的问题所在(再次强调，如果问题包括样本数据和所需输出，那么它为什么真的很有帮助)。