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 ?
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
Alternatively look at proc means, summary or just a one pass of the datastep to have better control of the order
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
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 !
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.