BookmarkSubscribeRSS Feed
subrat1
Fluorite | Level 6

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;

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

toconero
Fluorite | Level 6
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2335 views
  • 1 like
  • 4 in conversation