topic Re: how to merge two tables by two common variables in SAS Procedures

how to merge two tables by two common variables

m1986MM — Wed, 05 Nov 2014 15:30:22 GMT

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.

Re: how to merge two tables by two common variables

RW9 — Wed, 05 Nov 2014 15:44:45 GMT

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.*).

Re: how to merge two tables by two common variables

ballardw — Wed, 05 Nov 2014 15:49:46 GMT

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;

Re: how to merge two tables by two common variables

art297 — Wed, 05 Nov 2014 15:56:54 GMT

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!

Re: how to merge two tables by two common variables

m1986MM — Wed, 05 Nov 2014 16:01:11 GMT

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

Re: how to merge two tables by two common variables

m1986MM — Wed, 05 Nov 2014 16:04:53 GMT

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

Re: how to merge two tables by two common variables

RW9 — Wed, 05 Nov 2014 16:19:57 GMT

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

Re: how to merge two tables by two common variables

Tom — Wed, 05 Nov 2014 18:51:25 GMT

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;

Re: how to merge two tables by two common variables

sasjourney — Wed, 05 Nov 2014 20:12:58 GMT

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 Windows and UNIX environments