DATA Step, Macro, Functions and more

SQL and HASH

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

SQL and HASH

 

Can somebody assist me .....How following query can be translated into either SAS HASH code (preferred)

or SAS QUERY code?

 

Below is a given SQL NATIVE Query ( note that there is + sign in a where clause):


SELECT DISTINCT A1.acod as acode,
                               A1.descp as FDESC,
                             catx(' - ', B1.scod,B1.tcod) as stcode
FROM X X0,
           A A1,
          B B1,

          C C1

WHERE
         A1.OFFICEID(+)  = X0.officeid
AND B1.STATUSCD(+) = x0.STATUSCD 

AND C1.Post(+) = X0.Post 

AND C1.Pre(+)  = X0.Pre

AND X0.Pre(+)  = C1.Pre

AND X0.Post(+) = C1.Post

     ;

Quit;


Accepted Solutions
Solution
‎09-09-2017 11:58 AM
Super User
Posts: 8,219

Re: SQL and HASH

[ Edited ]

@LinusH: I think the (+)  indicates a left or right outer join depending upon whether it is on the left or right variable (see: https://chartio.com/resources/tutorials/left-and-right-joins-using-the-plus-sign-in-oracle/ )

 

Art, CEO, AnalystFinder.com

View solution in original post


All Replies
Super User
Posts: 5,919

Re: SQL and HASH

Giving you a SAS data step hash program tight will probably not help you to understand how to do it yourself next time. I suggest that you study it, give it a try, and then come back with more specific questions.

This SQL of yours, what does the + sign do/mean?
Data never sleeps
Solution
‎09-09-2017 11:58 AM
Super User
Posts: 8,219

Re: SQL and HASH

[ Edited ]

@LinusH: I think the (+)  indicates a left or right outer join depending upon whether it is on the left or right variable (see: https://chartio.com/resources/tutorials/left-and-right-joins-using-the-plus-sign-in-oracle/ )

 

Art, CEO, AnalystFinder.com

Super User
Posts: 5,919

Re: SQL and HASH

Oh.
Then I assume the SQL part of the question would resolve to - use LEFT/RIGHT JOIN...ON... in SAS (ANSI) SQL.
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 227 views
  • 1 like
  • 3 in conversation