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.
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
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.*).
Sorry! I didn't get the difference. You omitted the "as", like as L or as R. Is that the case
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
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;
No I changed the name of the variables here to make more sense. It's a typo.
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!
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.