总结 SAS 中的表格 [英] Summerizing a table in SAS

问题描述

proc sql;
update summary tmp1
set E_1ST_CLICK_DT = min (tmp1.E_1ST_CLICK_DT, (select E_1ST_CLICK_DT from CLICK_SUMM tmp2
                    where tmp1.card_number = tmp2.card_number
                        and tmp1.package_sk = tmp2.package_sk))
where exists (select 1 from CLICK_SUMM tmp2
                    where tmp1.card_number = tmp2.card_number
                        and tmp1.package_sk = tmp2.package_sk
            )
;
quit; 

我想从点击汇总表中获取第 1 次点击日期并将其填充到汇总表中.不幸的是，我无法对左连接进行更新，因为 sas 不支持它.有没有比上面显示的更好的方法来做到这一点，因为这实际上并不是一种有效的方法?我们可以改用数据步骤吗?

I want to get the 1st click date from the click summary table and populate it in summary table. Unfortunately I can't do a update on left join since sas doesn't support it. Is there a better way to do this than shown above since this is not really an efficient way of doing it? Can we use data step instead?

Summary 和 click_summ 表在 Oracle 中.

Summary and click_summ tables are in Oracle.

推荐答案

在 SAS 中有很多方法可以做到这一点.double set 语句适用于数据步骤中的左连接.

Many ways to do this in SAS. A double set statement works well for left joins in a data step.

我假设您不想从 E_1ST_CLICK_DT 中放入空值.如果不正确，请告诉我.

I assume you are not wanting to put nulls from E_1ST_CLICK_DT. If that is incorrect, let me know.

1. 过滤掉你不想要的空值
2. 在 CLICK_SUMM 表上为键添加索引.
3. Double Set 以加入值.

这提供了对大表的单次传递和对小表的随机访问.

This offers a single pass through the large table and random access into the smaller table.

data temp(index=(comp=(card_number package_sk)));
set click_summ(where=(^missing(e_1st_click_dt)));
keep card_number package_sk e_1st_click_dt;
run;

data summary(drop=new_date) ;
set summary;
set temp(rename=(e_1st_click_dt= new_date) in=a) key=comp;
if a then do;
   e_1st_click_dt = min(e_1st_click_dt,new_date);
end;
else
   _ERROR_ = 0; /*No need for IORC errors*/
run;

这篇关于总结 SAS 中的表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！