topic Re: proc sql, counts in SAS Programming

proc sql, counts

Xinxin — Thu, 29 Nov 2018 17:28:26 GMT

When I use this code:

proc sql; create table finn_counts1 as select *, sum(finn36) as ct_finn36 from finn1
group by ACCOUNT_ID; quit;

I get the correct sum (albeit it's for each row of ACCOUNT_ID, and I can dedup that) but why does the order of the rows for each ACCOUNT_ID change? It's not sorted by any variable, looks randomly changed.

Get these notes in the log:

NOTE: The query requires remerging summary statistics back with the original
data.
NOTE: SAS threaded sort was used.

Do I need to sort the data again to keep the original sequence per ACCOUNT_ID ?

Re: proc sql, counts

novinosrin — Thu, 29 Nov 2018 17:56:02 GMT

add on order by

order by account_id;

if you had used -

proc sql; 
create table finn_counts1 as
 select ACCOUNT_ID, sum(finn36) as ct_finn36
from finn1 
group by ACCOUNT_ID
order by ACCOUNT_ID;
 quit;

Proc sql wouldn't have remerged but I am assuming you perhaps want other variables besides the sum

Re: proc sql, counts

novinosrin — Thu, 29 Nov 2018 17:57:38 GMT

Alternatively look at proc means, summary or just a one pass of the datastep to have better control of the order

Re: proc sql, counts

kiranv_ — Thu, 29 Nov 2018 18:09:23 GMT

run both.

Intially dataset is ordersed by name. Once you want to group by sex all same gender are sorted so that summing of same becomes easy. this is part of underhood mechanism which you can understand by using _method

proc sql;
create table want as 
select * from sashelp.class;


proc sql;
create table want1 as 
select *, sum(age) from sashelp.class
group by sex;

/* then run _method*/


proc sql _method;
create table want1 as 
select *, sum(age) from sashelp.class
group by sex;

_method gives the following in log explaining how a group by is conducted.

sqxcrta
sqxsumg
sqxsort
sqxsrc( SASHELP.CLASS )

now the sort happens on gender as per the _method.

sqxceta -- creating final dataset

sqxsumg - sum within group

sqxsort-- sorting( done on your group by variable)

sqxsrc -source table

To havefinal dataset as per you please try using @novinosrin suggestion. Then dataset will be reordered as per your needs

Re: proc sql, counts

Xinxin — Thu, 29 Nov 2018 18:13:32 GMT

Wow...thanks...learnt something new!
Yes, I ended up ordering by ACCOUNT_ID and some other variables.
Thanks to both of you ,@kiranv_ and @novinosrin !