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;
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;
Tell PROC SQL to order by that sequence, in other words by MAKE and MODEL.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.