proc sql: summary functions using group by without changing the original order of the observations

[sasecn]

Wondered if there is a way to keep the same order of the observations when using summary functions in proc sql. For example, from sashelp.cars, I create a new table that has a new variable called "min_length', and the min is within "type". I believe the sashelp.cars is ordered by make first, then by other variables. If used the attached code to produce the new table, then compare two tables (exclude the new variable). I expect to see they should be the same, but I got the result that there are unequal obs. I think this is caused by the change in the order of obs.

proc sql;
create table new_cars as
select *, min(length) as min_length
from sashelp.cars
group by type
;
quit;

/* I think they should be the same if in the same oder */
proc compare base=sashelp.cars compare=new_cars (drop=min_length);
run;

[PaigeMiller]

Tell PROC SQL to order by that sequence, in other words by MAKE and MODEL.

[FreelanceReinh · Accepted solution]

Hi @sasecn,

As PaigeMiller suggested, you need to add an ORDER BY clause to your PROC SQL step. Otherwise, PROC SQL doesn't really care about sort order.

Now the problem is to find a suitable sort key. PROC CONTENTS assures us that SASHELP.CARS is sorted by Make Type, but PROC FREQ tells us that this is not a unique key, hence insufficient for our intended ORDER BY clause. Since there is no obvious unique sort key, we must probably create one (temporarily), e.g., in a view:

data _tmp / view=_tmp;
set sashelp.cars;
_seqno=_n_;
run;

proc sql;
create table new_cars(drop=_seqno) as
select *, min(length) as min_length
from _tmp
group by type
order by _seqno;
drop view _tmp;
quit;