使用SAS将不同的SQL合并到一张表中 [英] Combine different SQL into one table using SAS
本文介绍了使用SAS将不同的SQL合并到一张表中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想知道如何将不同的 SQL 查询合并到一个表中.
I would like to know how to combine different SQL queries into one table.
格式应该是
customer_no|TOTAL_DIFF_LASTPAYMENT_OPENED_dt|utilization trend| count_enquiry_recency_365|ratio_currbalance_creditlimit
我有两个包含数据的文件(请参阅数据示例).我想创建一个包含这些 PROC 输出的新表.
I have two files with data (see data sample). I want to create a new table that contains the output of these PROC's.
数据样本:https://www.dropbox.com/sh/k7qz6m8w0yqenn7/AAAgYcVhHxCRNhiX5ZvK0q5Aa?dl=0
/* TOTAL_DIFF_LASTPAYMENT_OPENED_dt */
proc sql;
select customer_no, avg(TOTAL_DIFF_LASTPAYMENT_OPENED_dt)
from account_30_sort
group by customer_no ;
quit;
<小时>
/* utilization trend */
proc sql;
select customer_no, sum (cur_balance_amt /creditlimit) / avg
(cur_balance_amt/(creditlimit+creditlimit))
from account_30_sort
group by customer_no;
quit;
<小时>
/* count_enquiry_recency_365 */
DATA WORK.ENQUIRY_30;
SET ENQUIRY_30;
num_days= dt_opened - enquiry_dt;
RUN;
proc sql;
select customer_no, sum(enq_amt)
from ENQUIRY_30
where num_days <=365
group by customer_no;
quit;
<小时>
/* ratio_currbalance_creditlimit */
proc sql;
select customer_no, sum( cur_balance_amt/ creditlimit)
from account_30
group by customer_no
;
quit;
推荐答案
您是否尝试加入 customer_no 上的结果?
Are you trying to join the result on customer_no?
proc sql;
create table want as
select t1.customer_no
,avg(t1.TOTAL_DIFF_LASTPAYMENT_OPENED_dt) as proc1
,sum(t1.cur_balance_amt /t1.creditlimit) / avg(t1.cur_balance_amt/(t1.creditlimit+t1.creditlimit)) as proc2
,sum(t2.enq_amt) as proc3
,sum(t3.cur_balance_amt/ t3.creditlimit) as proc4
from account_30_sort t1
,enquiry_30(where=(dt_opened - enquiry_dt <= 365)) t2
,account_30 t3
where t1.customer_no=t2.customer_no
and t2.customer_no=t3.customer_no
group by t1.customer_no
;
quit;
这篇关于使用SAS将不同的SQL合并到一张表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文