So let's use simpler names for you datasets and variables and eliminate that 7th letter from some of the id's that you aren't reading so the example is clearer.
So you have POLICY dataset that has the DATE variable. And a CONTRACT dataset that has the AMOUNT.
data policy;
length id $6 ;
input date :date. id;
format date date9. ;
cards;
24JAN2023 BzuMBB
24JAN2023 BzuPBB
20FEB2023 BzuWBB
15MAR2022 tmJQBQ
07Apr2023 tmLQBQ
12Aug2021 EtGBBU
27Oct2022 pAYBBY
;
data contract;
length id $6 contract $6. ;
input id contract amount;
cards;
BzuWBB P4QAO 138308
tmJQBQ PHQA4 100000
tmLQBQ PKQA4 61900
EtGBBU CkRE9 135292
nGxQBI OzQAO 40645
zbvQBC mGGAH 13786
;
You can put them together an group by the MONTH.
proc sql;
create table want as
select put(date,yymm7.) as Month
, count(distinct a.id) as n_ids
, count(distinct b.contract) as n_contracts
, sum(amount) as total
from policy a
left join contract b
on a.id = b.id
where date between '01JAN2021'd and '31DEC2023'd
group by 1
;
quit;
Results
Obs Month n_ids n_contracts total
1 2021M08 1 1 135292
2 2022M03 1 1 100000
3 2022M10 1 0 .
4 2023M01 2 0 .
5 2023M02 1 1 138308
6 2023M04 1 1 61900
... View more
