DATA Step, Macro, Functions and more

Data merge and hash join

Reply
Contributor
Posts: 47

Data merge and hash join

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;

Super User
Posts: 6,964

Re: Data merge and hash join

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,415

Re: Data merge and hash join

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.

Occasional Contributor
Posts: 8

Re: Data merge and hash join

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
Ask a Question
Discussion stats
  • 3 replies
  • 210 views
  • 1 like
  • 4 in conversation