Help using Base SAS procedures

how to merge two tables by two common variables

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

how to merge two tables by two common variables

Dear everyone,

I tried to merge two datasets, using "proc sql". Two datasets have two common variables: ID and Sharetype. Below is my code:

proc sort data=Institution

by ID Sharetypr;

proc sort data=Stock;

by ID sharetype;

run;

proc sql;

create table Institution_Stock as

select L.*, R.*

from Institution as L

LEFT JOIN Stock as R

on L.ID=R.ID and L.sharetype=R.sharetype;

quit;

The sort part is run without any error, but when I run the proc sql I receive this error:

"ERROR: Sort execution failure."

I have no idea what's the problem. I appreciate if you help me.


Accepted Solutions
Solution
‎11-05-2014 11:19 AM
Super User
Super User
Posts: 7,413

Re: how to merge two tables by two common variables

There isn't much difference, I point out that doing R.* will give you warnings as variables exist in two different datasets.  You can put AS in if you like, I tend to not do that, just a preference.  Per my message, you don't need the sorts so just run the code.  If you get errors other than variable appears in both tables, then please post some test data and the full warning without spelling mistakes, also stating what version/install of SAS you have, is it server based/locally installed.  If you still get the error then perhaps something on this page will help; http://support.sas.com/kb/39/705.html

View solution in original post


All Replies
Super User
Super User
Posts: 7,413

Re: how to merge two tables by two common variables

Hi,

Well firstly you do not need to pre-sort datasets, SQL does not expect pre-sorted datasets.  Now your SQL:

proc sql;

     create table Institution_Stock as

     select     L.*,

                    R.*    /* You don't want to do this as variables appear in both datasets */

     from Institution L

     left join  Stock R

     on L.ID=R.ID

     and L.sharetype=R.sharetype;

quit;

The above should work on unsorted datasets, if it doesn't post some test data. (note you will get a warning per my comment about r.*).

Contributor
Posts: 65

Re: how to merge two tables by two common variables

Sorry! I didn't get the difference. You omitted the "as", like as L or as R. Is that the case

Solution
‎11-05-2014 11:19 AM
Super User
Super User
Posts: 7,413

Re: how to merge two tables by two common variables

There isn't much difference, I point out that doing R.* will give you warnings as variables exist in two different datasets.  You can put AS in if you like, I tend to not do that, just a preference.  Per my message, you don't need the sorts so just run the code.  If you get errors other than variable appears in both tables, then please post some test data and the full warning without spelling mistakes, also stating what version/install of SAS you have, is it server based/locally installed.  If you still get the error then perhaps something on this page will help; http://support.sas.com/kb/39/705.html

Super User
Posts: 10,538

Re: how to merge two tables by two common variables

If this is the exact code submitted you are missing a ; after the dataset name (or at least before the BY statement).

proc sort data=Institution

by ID Sharetypr;

Contributor
Posts: 65

Re: how to merge two tables by two common variables

No I changed the name of the variables here to make more sense. It's a typo.

PROC Star
Posts: 7,364

Re: how to merge two tables by two common variables

As my colleagues have already pointed out, your first proc sort call is missing a semicolon. Additionally, in your first proc sort call you misspelled Sharetype as Sharetypr. And, as they also pointed out, your data doesn't have to be sorted in order to run proc sql.

Your sql code, I think, will run as is!

Super User
Super User
Posts: 6,502

Re: how to merge two tables by two common variables

Why not just do a merge instead of using PROC SQL ??

data Institution_Stock;

  merge Institution (in=in1) Stock ;

  by ID sharetype ;

   if in1;

run;

Contributor
Posts: 43

Re: how to merge two tables by two common variables

Hi m,

As everyone said above, if there are no typo's your code should work as is or this could be a memory issue too. The below SAS notes might help in fixing the error

39705 - Troubleshooting Sort Initialization and Sort Execution Failure errors in SQL procedures in W...

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 3139 views
  • 9 likes
  • 6 in conversation