I am still not sure on the structure of Have and want datasets. Assuming that Have will have the client_name and want_mmdd contains client_name, bill and pay value, here is a dataset to consolidate the pay and bill values.
%let cur_month=%sysfunc(month(%sysfunc(today()))); data result;
retain pay_idx 1 bill_idx 2 ; array agg[3,2] 8 _temporary_; /* As an example i took 3 clients.*/ if _n_=1 then do; dcl hash clients(); clients.defineKey('client_name'); clients.defineData('client_idx'); clients.defineDone(); do until(clientsEof); set have end=clientsEof; client_idx+1; clients.add(); end; end; %do month=%sysevalf(&cur_month.-6) %to &cur_month.; %do date=1 %to 31; %if %sysfunc(exist(work.want_%sysfunc(putn(&month.,z2.))%sysfunc(putn(&date.,z2.)))) %then %do; do until (eof); set want_%sysfunc(putn(&month.,z2.))%sysfunc(putn(&date.,z2.)) end=eof; clients.find(); agg[client_idx,pay_idx]=sum(agg[client_idx,pay_idx],pay); agg[client_idx,bill_idx]=sum(agg[client_idx,bill_idx],bill); end; %end; %end; %end;
/*use client_idx to query the summarized value for each client */ agg_bill=agg[client_idx,bill_idx]; agg_pay=agg[client_idx,pay_idx];
/* Calculate here */ run;
Not sure if this helps!
... View more