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

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.

1 ACCEPTED SOLUTION

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

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

m1986MM
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

ballardw
Super User

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;

m1986MM
Obsidian | Level 7

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

art297
Opal | Level 21

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!

Tom
Super User Tom
Super User

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;

sasjourney
Calcite | Level 5

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 17190 views
  • 9 likes
  • 6 in conversation