How to group by result for the same date-CodePudding

I have two queries, where the first gathers information, the second should sum up the results. However, the code shows me duplicate results. For example, for 2021-10 I have several records, I would like there to be one record for each month.

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;

Here is output which i received. but its wrong:

CodePudding user response：

GROUP BY does not group by formatted values. Some procedures do.

Try

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

CodePudding user response：

It's resolved. Should changed date to string

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

CodePudding user response：

What is you think the first query is creating? Why are you grouping by variables not included in the output? Why does your CASE not account for dates that are more than 7 and less than 30 days after the target date?

Perhaps you wanted to do something like this?

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"
;