DATA Step, Macro, Functions and more

avoid sorting

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

avoid sorting

Hi All,

I want to avoid sorting while doing merge because my below tables are already in sorting order. I think index will help but I am not sure.

or is there any method if the tables are already in sorting order then merge or sort kind of approach.

proc sort data=a ;
			by id;
		run;

		proc sort data=b;
			by id;
		run;

		data b;
			merge  a(in=a) b(in=b);
					by id;
					if a=b ;
		run;

Thanks,

 

SS


Accepted Solutions
Solution
‎04-18-2018 10:07 AM
Super User
Posts: 9,855

Re: avoid sorting


@sathya66 wrote:
but it is showing an error.
ERROR: BY variables are not properly sorted on data set WORK.A.

Then your dataset is NOT sorted.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 6,622

Re: avoid sorting

If your data sets are already in order, you don't need to run PROC SORT.  Just proceed directly to the DATA step with MERGE.

 

The BY statement requires a data set that is in order.  It doesn't matter how the data set came to be in order.  It does not require sorting, if the data set is already in order.

Frequent Contributor
Posts: 75

Re: avoid sorting

Posted in reply to Astounding
but it is showing an error.
ERROR: BY variables are not properly sorted on data set WORK.A.
Super User
Posts: 6,622

Re: avoid sorting

If you are getting that error message, it means the observations are not in order.  Of course you need to run PROC SORT when the observations are not in order.  I thought you were asking if you could skip the PROC SORT when the data set was already in order.

Solution
‎04-18-2018 10:07 AM
Super User
Posts: 9,855

Re: avoid sorting


@sathya66 wrote:
but it is showing an error.
ERROR: BY variables are not properly sorted on data set WORK.A.

Then your dataset is NOT sorted.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,855

Re: avoid sorting

Maxim 2: Read the log. If the tables are already sorted as you need them, this will show in the log. Then no additional sorting needs to be done.

Indexes only improve performance if they can be used to select small subsets of data; in whole-dataset joins like yours they usually worsen overall performance.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 514

Re: avoid sorting

Adding the option "presorted" to proc sort, will prevent datasets from being sorted if they are already sorted.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 99 views
  • 0 likes
  • 4 in conversation