Hash object Aggregation from multiple sources

Py14ak1138 — Wed, 06 Oct 2021 14:08:42 GMT

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;

Re: Hash object Aggregation from multiple sources

Oligolas — Wed, 06 Oct 2021 15:22:23 GMT

Hi and welcolme to the SAS communities forum.

In order to debug this you need to gradually increment the complexity.

By posting appropriate test data you will increase your chances to get an answer that suits your needs.

I do not understand right now how you can start with

sdjisdjis|233  |0  |

look in the separate libraries that do NOT contain the id

sdjisdjis

|Limit_ref|Off_ballance_exposure|
3dssdasdda|                 4343|
3dssdasdda|                 7777|
dfdfdfdfdf|               488.12|
dfdfdfdfdf|                8.10 |

and expect to get a sum of 344!!

sdjisdjis|233 | 344|
sdjisdjis|234 |  0 |
sdjisdjis|235 |  0 |

Please format your post in a more readable manner.

Please post some test data you have and the results you'd expect

topic Re: Hash object Aggregation from multiple sources in SAS Programming

Hash object Aggregation from multiple sources

Re: Hash object Aggregation from multiple sources