registros numerar funciones ejemplos contar comandos sql sql-server sas sas-macro

numerar - proc sql sas ejemplos



Combina diferentes SQL en una tabla usando SAS (1)

¿Estás intentando unir el resultado en 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;

Me gustaría saber cómo combinar diferentes consultas SQL en una sola tabla.

El formato debe ser

customer_no|TOTAL_DIFF_LASTPAYMENT_OPENED_dt|utilization trend| count_enquiry_recency_365|ratio_currbalance_creditlimit

Tengo dos archivos con datos (ver muestra de datos). Quiero crear una nueva tabla que contenga el resultado de estos PROC.

Ejemplo de datos: 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;