DATA Step, Macro, Functions and more

Proc SQL Left Inner Join

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Proc SQL Left Inner Join

Hello.

If there a way to do a left inner join, and the table produced from this procedure is not sorted.  It would be great to keep it in the order before the merge.

Thank you so much!


Accepted Solutions
Solution
‎02-06-2015 09:51 AM
Super User
Super User
Posts: 7,392

Re: Proc SQL Left Inner Join

Assign and order variable before merging:

proc sql;

     create table WANT as

     select     A.*,

                    B.X

     from        (select *,MONOTONIC() as ORD from HAVE1) A

     left inner join HAVE2

     on           A.IDVAR1=B.IDVAR2

     order by  ORD;

quit;

Just to add though, if you want proper ordering setup variables for order before merging as "the order it was before merging" isn't a logical/replicate able statement.

View solution in original post


All Replies
SAS Employee
Posts: 340

Re: Proc SQL Left Inner Join

If the other table is not so big (fits into memory), you can write a data step and use a hash object.

Solution
‎02-06-2015 09:51 AM
Super User
Super User
Posts: 7,392

Re: Proc SQL Left Inner Join

Assign and order variable before merging:

proc sql;

     create table WANT as

     select     A.*,

                    B.X

     from        (select *,MONOTONIC() as ORD from HAVE1) A

     left inner join HAVE2

     on           A.IDVAR1=B.IDVAR2

     order by  ORD;

quit;

Just to add though, if you want proper ordering setup variables for order before merging as "the order it was before merging" isn't a logical/replicate able statement.

Contributor
Posts: 29

Re: Proc SQL Left Inner Join

I apologize for the last entry.  I didn't know the code you posted was the answer.

Thank you again!

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 287 views
  • 2 likes
  • 3 in conversation