topic how to use hash object for data integration & data aggregation in SAS Programming

how to use hash object for data integration & data aggregation

Bharat_P — Mon, 30 Dec 2019 11:18:38 GMT

proc sql;
create table SUMM3_REPEAT_CUST as
select M.CNCPT_NM
, M.TRRTRY_SHRT_NM
, (C.REPEAT_CUST/M.CUST_CY) as REPEAT_CUST_PER_CY format=percent8.2
, (L.REPEAT_CUST/M.CUST_LY) as REPEAT_CUST_PER_LY format=percent8.2
, (calculated REPEAT_CUST_PER_CY - calculated REPEAT_CUST_PER_LY) as REPEAT_CUST_PP_CHNG
, (C.REPEAT_CUST_REV/M.REV_CY) as REPEAT_CUST_REV_PER_CY format=percent8.2
, (L.REPEAT_CUST_REV/M.REV_LY) as REPEAT_CUST_REV_PER_LY format=percent8.2
, (calculated REPEAT_CUST_REV_PER_CY - calculated REPEAT_CUST_REV_PER_LY) as REPEAT_CUST_REV_PP_CHNG
from SUMM1_REV_TREE M
left join REPEAT_CUST_CY C on M.CNCPT_NM=C.CNCPT_NM and M.TRRTRY_SHRT_NM=C.TRRTRY_SHRT_NM
left join REPEAT_CUST_LY L on M.CNCPT_NM=L.CNCPT_NM and M.TRRTRY_SHRT_NM=L.TRRTRY_SHRT_NM
order by 1,2
;
quit;

This is a sample code which I want to covert to hash objects but due to few study materials available online regarding Hash coding. I'm not being able to find a solutaion. someone please help me out ASAP

Re: how to use hash object for data integration & data aggregation

LinusH — Mon, 30 Dec 2019 12:59:24 GMT

I don't see any aggregation, only joining an row level calculations.

Why do you need a hash table? Performance issuues (then share the log with PROC SQL _tree/_method).

Why ASAP?

And, what have you tried so far, and what is your challenge?

If you don't how to start at all, revert to examples and training.

Re: how to use hash object for data integration & data aggregation

Bharat_P — Tue, 31 Dec 2019 05:14:40 GMT

Forget the integration part I wrote.I want to join the same tables with hash objects. The code I shared is a part of many such joining I want to do with Hash coding. If I get a solution for this then based on this code I can manage to do the other joining as well. If you or anyone can manage to help me..it will be great

Re: how to use hash object for data integration & data aggregation

Patrick — Tue, 31 Dec 2019 07:24:15 GMT

@Bharat_P wrote:

Forget the integration part I wrote.I want to join the same tables with hash objects. The code I shared is a part of many such joining I want to do with Hash coding. If I get a solution for this then based on this code I can manage to do the other joining as well. If you or anyone can manage to help me..it will be great

I guess you're after actual code and not just narrative. If so then please post sample data for all the source tables (in the form of working and tested SAS data step code) so we can spend the time for the solution.

Please make sure the sample data has actually matching keys - and also explain the the relationships between the tables (1:1, 1:N, ....).

For your real data: The tables for the left joins must fit into memory. What data volumes are you actually dealing with?

I like the options SAS hash tables provide but it's not a fit-for-all approach. What's your reason for wanting to change the SQL to data step a hash lookup?

As for learning to use the hash object:

Besides of the SAS documentation there are quite a few white papers out there (not hard to Google) plus a great book.