topic Re: Views and "Cannot sort in place" error in SAS Programming

Views and "Cannot sort in place" error

Phil_NZ — Fri, 12 Mar 2021 22:11:43 GMT

Hi all SAS Users,

I am new to VIEW, even I am trying to read some documents about that but still a novice in that part.

Today I faced a problem named

ERROR: Cannot sort in place.

So, there are two scenarios, the first one (without views) it can run smoothly, the second one (with views) it runs with an error

The first scenario

data trim_price ;/*without views - this is the only difference between two codes*/
	set temp;
	 if 0<rank<99 or year=1999;
run;
*sort by country;
proc sort data=work.trim_price;
by LOC year;
run;

The log

37         *removing stocks if yearly amihud
38         in the top 1% within a country each year;
39         
40         *sort by country;
41         proc sort data=work.trim_price;
42         by LOC year;
43         run;

NOTE: There were 52599 observations read from the data set WORK.TRIM_PRICE.

The second one

data trim_price / view=trim_price;
	set temp;
	 if 0<rank<99 or year=1999;
run;
*sort by country;
proc sort data=work.trim_price;
by LOC year;
run;

The log

ERROR: Cannot sort in place.
ERROR: Unable to create WORK.TRIM_PRICE.DATA because WORK.TRIM_PRICE.VIEW already exists.

Could you please explain to me the reason behind this? And whether this error is caused just because of the view or is there any possible hidden error?

Warmest regards and have a nice weekend.

Re: Views and "Cannot sort in place" error

ballardw — Fri, 12 Mar 2021 22:19:25 GMT

Remember that Views are instructions on how to pull data from some other source.

So you can't change the order of the data in that instruction set.

You can specify an OUT= data set in proc sort but that may make the reason you are attempting to use a view moot.

If you know that you want a specific order in the set you might consider creating your view using Proc SQL with an Order By clause:

proc sql;
   create view sorted as
   select * 
   from sashelp.class
   order by sex, age, weight
   ;
quit;


proc print data=sorted;
run;

Re: Views and "Cannot sort in place" error

Phil_NZ — Fri, 12 Mar 2021 22:26:44 GMT

Hi @ballardw

Thank you for your suggestion, I am curious about that part

You can specify an OUT= data set in proc sort but that may make the reason you are attempting to use a view moot.

So, it equals to my first scenario( without view) regarding consuming the same storage, isn't it? I hope that I explain your idea properly.

Warm regards.

Re: Views and "Cannot sort in place" error

SASKiwi — Fri, 12 Mar 2021 22:27:25 GMT

Sorting a view in-place doesn't make sense and isn't allowed. You've created the view based on a different table. If you want the data to come out in a different order sort the TEMP table and recreate the view.

If you want to sort the data coming out of the view use the OUT = option in your PROC SORT to store the sorted data in a different output dataset.

Re: Views and "Cannot sort in place" error

Phil_NZ — Fri, 12 Mar 2021 23:07:56 GMT

Hi @SASKiwi

If I want to keep the dataname, can I do that?

data trim_price ;/*without views - this is the only difference between two codes*/
	set temp;
	 if 0<rank<99 or year=1999;
run;
*sort by country;
proc sort data=work.trim_price;
by LOC year;
run;

Warm regards.

Re: Views and "Cannot sort in place" error

SASKiwi — Sat, 13 Mar 2021 03:03:06 GMT

@Phil_NZ - Really you don't need to ask. Just run it and if it works without error then of course that is fine. Updating in place with real datasets is perfectly OK.

Remember a view is essentially a read-only "window" into the original data behind it. If you wanted to change what you saw through a shop window would you change the window or what was behind it? The concept is the same.

Re: Views and "Cannot sort in place" error

Patrick — Sat, 13 Mar 2021 03:44:06 GMT

If you want to create a view which returns the rows sorted then consider below:

proc sql feedback;
  create view work.trim_price as
  select *
  from temp
  where 0<rank<99 or year=1999
  order by loc, year
  ;
quit;

Re: Views and "Cannot sort in place" error

Tom — Sat, 13 Mar 2021 04:15:15 GMT

You are sort the wrong thing. If you want to change the order the data is returned by the data step view then change the order of the order of the data that the view reads.

proc sort data=temp;
  by LOC year;
run;