topic Re: Data merge and hash join in SAS Programming

Data merge and hash join

subrat1 — Wed, 27 Jul 2016 12:05:27 GMT

I have a proc sql code as shown below, i want to do that sql code using data step Merge and Hash join

PROC SQL;
CREATE TABLE CURRENT.TCOVERAGES_SUMMARY_DATE AS
  SELECT
   POLICY_TYPE_SK,
   SOURCE_SYSTEM_SK,
   ACTUAL_DT,
   YEAR(DATEPART(ACTUAL_DT)) AS YEAR_CT,
   MONTH(DATEPART(ACTUAL_DT)) AS MONTH_CT,
   INTCOV_SK,
   POLICY_SK,
   ITEM_ATTRIBUTE_SK,
   BROKER_NM,
   EARNED_XPSR_AM,
   WRITTEN_XPSR_AM,
   MTD_WRITTEN_PRM_AM,
   EARNED_PRM_AM
  FROM CURRENT.TCOVERAGES_SUMMARY T1
   LEFT JOIN CURRENT.TDATE T2
    ON T1.MONTH_SK = T2.DATE_SK
   LEFT JOIN CURRENT.TBROKER_DIMENSION T3
    ON T3.BROKER_SK = T1.BROKER_SK;
WHERE   POLICY_TYPE_SK IN (1,15) AND
  SOURCE_SYSTEM_SK NOT IN (2,3,4,14,15) AND
  SNAPSHOT_TYPE_IN = 0 AND
  YEAR(DATEPART(ACTUAL_DT)) >= 2015 AND
  YEAR(DATEPART(ACTUAL_DT)) <= 2016;
QUIT;

Re: Data merge and hash join

Kurt_Bremser — Wed, 27 Jul 2016 12:16:51 GMT

Could you provide some information about the data structures (variables, number of records) of the datasets?

Re: Data merge and hash join

RW9 — Wed, 27 Jul 2016 12:17:47 GMT

So what is the question? If you don't know hash then perhaps start with the manual and examples on the web, and come back with any questions.

Re: Data merge and hash join

toconero — Wed, 27 Jul 2016 12:44:08 GMT

Proc Sql; is more efficient making Left Join but if you're interesting at making a Hash join:
Here there are two links:
http://www.lexjansen.com/nesug/nesug06/dm/da07.pdf
http://stackoverflow.com/questions/34789361/sas-proc-sql-left-join-to-hash-object (really good post).
good luck