如何按同一日期的結果分組-有解無憂

我有兩個查詢，第一個收集資訊，第二個應該總結結果。但是，代碼顯示了重復的結果。例如，對于 2021-10，我有幾條記錄，我希望每個月都有一條記錄。

PROC SQL;     
create table PolisyEnd as 
    select distinct       
    (t4.spr_NRB) as NRB
    ,datepart(t1.PRP_END_DATE) as POLICY_VINTAGE format yymmd7.,

case
    when datepart(t1.PRP_END_DATE) IS NOT NULL and datepart(t1.PRP_END_DATE) - &gv_date_dly. < 0 THEN 'W' 
    when datepart(t1.PRP_END_DATE) IS NOT NULL and datepart(t1.PRP_END_DATE) - &gv_date_dly. >= 0 and datepart(t1.PRP_END_DATE) - &gv_date_dly. <=7 THEN 'U' 
    when datepart(t1.PRP_END_DATE) IS NOT NULL and datepart(t1.PRP_END_DATE) - &gv_date_dly. >= 30 THEN 'A' 
    when datepart(t1.PRP_END_DATE) IS NULL THEN 'NO INFO' 

    end as POLISA_

from 
    cmz.WMDTZDP_BH t1
left join 
        (select distinct kontr_id,obj_oid from cmz.BH_D_ZAB_X_ALOK_&thismonth) t2 
on t2.obj_oid = t1.obj_oid

left join 
        (select distinct data_danych, kontr_id, kre_nrb from dm.BH_WMDTKRE_&thismonth) t3
on t3.kontr_id = t2.kontr_id
 
left join 
        (select distinct spr_NRB, spr_STATUS from _mart.mart_kred) t4
on t4.spr_NRB = t3.kre_nrb 


where datepart(t1.data_danych) between '5Aug2019'd and &gv_date_dly. and t1.Actual = "T"
and t4.spr_STATUS  ="A"
group by 
    datepart(t1.data_danych)   
    ,datepart(t1.PRP_END_DATE)

;

quit;
PROC SQL;   
    create table PolisyEnd1 as
    select distinct
    POLICY_VINTAGE
    ,count(NRB) as NUMBER
    from PolisyEnd
    where POLISA_INFORMACJA ="A"
    group by POLICY_
;
Quit;

這是我收到的輸出。但它錯了：如何按同一日期的結果分組

uj5u.com熱心網友回復：

GROUP BY 不按格式化的值分組。 有些程式可以。

嘗試

group by 
    put(datepart(t1.data_danych), yymmd7.)
   ,put(datepart(t1.PRP_END_DATE), yymmd7.)

uj5u.com熱心網友回復：

解決了。應該將日期更改為字串

    ,put(datepart(t1.PRP_END_DATE),yymmd7.) as POLICY_VINTAGE,

uj5u.com熱心網友回復：

您認為第一個查詢正在創建什么？為什么要按輸出中未包含的變數進行分組？為什么您的 CASE 不考慮在目標日期之后超過 7 天且少于 30 天的日期？

也許你想做這樣的事情？

create table PolisyEnd as 
  select distinct       
   (t4.spr_NRB) as NRB
  ,datepart(t1.data_danych) as DATA_DANYCH_DATE format yymmdd10.
  ,intnx('month',datepart(t1.PRP_END_DATE),0) as POLICY_VINTAGE format yymmd7.
  ,case
    when t1.PRP_END_DATE IS NULL THEN 'NO INFO' 
    when datepart(t1.PRP_END_DATE) < &gv_date_dly. THEN 'W' 
    when datepart(t1.PRP_END_DATE) <= &gv_date_dly.   7 THEN 'U' 
    when datepart(t1.PRP_END_DATE) >= &gv_date_dly.   30 THEN 'A' 
    else ' ' /* Between 7 and 30 days */
   end as POLISA_
from cmz.WMDTZDP_BH t1
left join (select distinct kontr_id,obj_oid from cmz.BH_D_ZAB_X_ALOK_&thismonth) t2 
  on t2.obj_oid = t1.obj_oid
left join (select distinct data_danych, kontr_id, kre_nrb from dm.BH_WMDTKRE_&thismonth) t3
  on t3.kontr_id = t2.kontr_id
left join (select distinct spr_NRB, spr_STATUS from _mart.mart_kred) t4
  on t4.spr_NRB = t3.kre_nrb 
where datepart(t1.data_danych) between '5Aug2019'd and &gv_date_dly.
  and t1.Actual = "T"
  and t4.spr_STATUS  ="A"
;

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/339074.html

標籤：sql SAS

上一篇：無法識別數值“{userId}”

下一篇：2021-10-27