BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dtchoi86
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

3 REPLIES 3
gergely_batho
SAS Employee

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

dtchoi86
Fluorite | Level 6

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

Thank you again!

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
  • 1439 views
  • 2 likes
  • 3 in conversation