@Quentin @PaigeMiller This is what i started with. For each of the Year&Month in Amount_By_Year table, I want the &members. for that period and Producers for that period (which is conditional on Contacts table). This code is not working as expected so that's why I need help. Thanks! %let StartDate = '01Jul2021'd;
%let EndDate = '01Jun2023'd;
proc sql;
create table Contacts as
select Id,
Name,
First_Issued_Policy__c
from Contact
where ((RecordTypeId='hKQCQ' and CreatedDate<=&StartDate.)
or (RecordTypeId='fgQCA' and CreatedDate<&StartDate. and (First_Issued_Policy__c>=&StartDate. or First_Issued_Policy__c is null))
;
quit;
proc sql;
select count(Id) into :Members
from Contacts;
quit;
proc sql;
create table Amount_by_Year as
select year(a.Record_Date__c) as Year,
month(a.Record_Date__c) as Month,
sum(b.Amount) as Amount,
count(distinct b.Contact__c) as Producers,
sum(b.Amount)/count(distinct b.Contact__c) as Avg_per_Producer,
&Members. as Members,
sum(b.Amount)/&Members. as Avg_per_Member
from Policy_Std a left join Policy_Agent_Std b on a.Id = b.Opportunity__c
where b.Contact__c in (select Id from Contacts)
and '01Jul2021'd <= a.Record_Date__c < '01Jul2023'd
group by 1, 2;
quit;
... View more