topic Re: avoid sorting in SAS Programming

avoid sorting

sathya66 — Wed, 18 Apr 2018 09:03:29 GMT

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,

Re: avoid sorting

Astounding — Wed, 18 Apr 2018 09:14:56 GMT

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.

Re: avoid sorting

Kurt_Bremser — Wed, 18 Apr 2018 09:18:37 GMT

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.

Re: avoid sorting

andreas_lds — Wed, 18 Apr 2018 09:31:28 GMT

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

Re: avoid sorting

sathya66 — Wed, 18 Apr 2018 09:40:59 GMT

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

Re: avoid sorting

Astounding — Wed, 18 Apr 2018 09:43:00 GMT

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.

Re: avoid sorting

Kurt_Bremser — Wed, 18 Apr 2018 09:43:04 GMT

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