topic Re: proc sql: summary functions using group by without changing the original order of the observatio in SAS Procedures

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

sasecn — Mon, 02 Aug 2021 15:52:27 GMT

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;

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

PaigeMiller — Mon, 02 Aug 2021 16:21:21 GMT

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

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

FreelanceReinh — Mon, 02 Aug 2021 16:46:12 GMT

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;