I have to process a cumulative sum located in different libraries with different names based on the logic in set "te".
The keys set contains unique combinations of a character string and a integer number.
set keys:
Limit_ref|month|sum|
sdjisdjis|233 |0 |
////////////////////
The sets with information for these clients is located in separate libraries. The data in the sets is daily transactions as follows.
|Limit_ref|Off_ballance_exposure|
3dssdasdda| 4343|
3dssdasdda| 7777|
dfdfdfdfdf| 488.12|
dfdfdfdfdf| 8.10 |
/////////////////////////////////
I want to sum the transactions for each set and add a second identifier month so that the Hash object contains the following data.
Limit_ref|month|sum|
sdjisdjis|233 | 344|
sdjisdjis|234 | 0 |
sdjisdjis|235 | 0 |
sdjisdjis|236 |18.99|
dfdfdfdfd|233 | 312 |
dfdfdfdfd|234 | 0 |
//////////////////////
And I want to only keep those with lim reference and month found in the original keys set.
How I have structured each look up:
do until(lr_61); set rwa38.FINAL_RWA_200801(keep=limit_reference OFF_BAL_EXPOSURE_AMOUNT )end=lr_61; month=61; sum=0; rc_cf=h_cf.find(); if rc_cf=0 then sum=sum; else sum=sum(sum,OFF_BAL_EXPOSURE_AMOUNT); h_cf.replace();end; What is happening is that it is gathering more accounts than what I want and I'm not sure I'm summing the file and then just replacing the final sum or just picking a random sum from the file. In the final lookup non of the sums show up. data te;
length guz2 $255.;
do var=&startper.+1 to &endper.;
a=var-23;
monthdate=intnx("month", "31dec2002"d, var);
yyyymm=catt(year(monthdate), put(month(monthdate),z2.));
guz=cats('rwa',a,'.FINAL_RWA_',yyyymm,'(keep=limit_reference OFF_BAL_EXPOSURE_AMOUNT )');
guz2=cats("do until(lr_",var,");","set rwa",a,".FINAL_RWA_",yyyymm,"(keep=limit_reference OFF_BAL_EXPOSURE_AMOUNT )","end=lr_",var,";","month=",var,";sum=0;rc_cf=h_cf.find();if rc_cf=0 then sum=sum;","else sum=sum(sum,OFF_BAL_EXPOSURE_AMOUNT);h_cf.replace();end;");
output;
end;
run;
proc sql ;
select guz into :ACAB separated by ' ' from te;
quit;
proc sql ;
select guz2 into :ACAB2 separated by ' ' from te;
quit;
data te;
run;
data keys;
length limit_reference $20.;
set revnew.scored_all_06(keep=limit_reference month);
/* rename number=month;*/
sum=0;
run;
proc sort data=keys nodupkey;
by limit_reference month;
run;
DATA _null_;
if _N_= 0 then
set keys &ACAB.;
if _N_ eq 1 then
do;
declare hash h_cf(dataset:'keys',hashexp:15,multidata:'N');
h_cf.defineKey('limit_reference','month');
h_cf.defineData(all:'Y');
h_cf.defineDone();
end;
&ACAB2.
;
h_cf.output(dataset:"RWA_payments");
/* stop;*/
run;
DATA revnew.scored_all_06;
if _N_= 0 then
set revnew.scored_all_06 RWA_payments;
do;
declare hash h_cf(dataset:'RWA_payments',multidata:'N',hashexp:15,ordered:'A');
h_cf.defineKey('limit_reference','month');
h_cf.defineData('limit_reference','month','sum');
h_cf.defineDone();
end;
set revnew.scored_all_06;
rc_cf=h_cf.find();
if rc_cf=0 then
output;
if rc_cf ne 0 then
do; sum=.;
output;
end;
run;
... View more
