topic Re: Sort rows in SAS Programming

Sort rows

Ronein — Thu, 19 Dec 2019 06:15:14 GMT

Hello

Please find summary table.

I need to do 2 things and I would like to ask please how to do them:

1-I want to sort the data in following order:

833RET

847ABC

958THF

855DBC

860QWE

2-I want to add summary row for following groups:

833RET+847ABC

958THF+855DBC+860QWE

All groups together

(Note: In summary row there will be only value of SUM_Y and mean_Y value will be missing)

So there will be 8 rows in the required data set (5 original rows plus 3 of sum )

row1-833RET

Row2-847ABC

Row3-833RET+847ABC

Row4-958THF

Row5-855DBC

Row6-958THF+855DBC

Row7-860QWE

Row8-Grand Total for all groups

Data  summary_tb;
input group $ mean_Y  SUM_Y ;
cards;
847ABC 10.1 1200 
958THF 19.1 1580
833RET  17.2 1620
860QWE 14.8 1490
855DBC 15.1 1810
;
run;

Re: Sort rows

Kurt_Bremser — Thu, 19 Dec 2019 06:16:33 GMT

What is the logical rule for sorting, and what is the logical rule for building the groups? Without such, no code is possible.

Re: Sort rows

Ronein — Thu, 19 Dec 2019 07:04:11 GMT

Hello

In the real report that I need to produce the order of the rows is important.

Lt's say that the "important groups" should be in the first rows of the report.

I have tried to create another data set that showing the order of rows that I want but unfortunately the required data set still has wrong order of rows.

I want to ask also why in the left join the sort of the rows is not in same order as the table on the left?

Data  summary_tbl;
input group $ mean_Y  SUM_Y ;
cards;
847ABC 10.1 1200 
958THF 19.1 1580
833RET  17.2 1620
860QWE 14.8 1490
855DBC 15.1 1810
;
run;


Data ssort;
input group $;
cards;
833RET
847ABC
958THF
855DBC
860QWE
;
run;

PROC SQL;
	create table required  as
	select a.*,b.mean_Y,b.SUM_Y 	   
	from  ssort as a
	left join summary_tbl as b
	on a.group=b.group
;
QUIT;

Re: Sort rows

Kurt_Bremser — Thu, 19 Dec 2019 08:02:54 GMT

The SQL join does an implicit sort on the "key" column(s) used in the "on" clause.

If you need to preserve the original order, you need to store that in an additional column:

data  summary_tbl;
input group $ mean_Y  SUM_Y ;
cards;
847ABC 10.1 1200 
958THF 19.1 1580
833RET  17.2 1620
860QWE 14.8 1490
855DBC 15.1 1810
;
run;

data ssort;
input group $;
sortkey = _n_;
cards;
833RET
847ABC
958THF
855DBC
860QWE
;
run;

proc sql;
create table required  as
select a.group,b.mean_Y,b.SUM_Y 	   
from  ssort as a
left join summary_tbl as b
on a.group=b.group
order by a.sortkey
;
quit;

Re: Sort rows

andreas_lds — Thu, 19 Dec 2019 08:12:41 GMT

Fixing the sorting-problem:

data SortFormat;
   set ssort(rename=(group=Start));
   length FmtName $ 32 Label $ 3 _number 8;
   retain FmtName "$RequiredOrder" _number 0;

   _number = _number + 1;
   Label = put(_number, z3.);

   drop _number;
run;

proc format cntlin=SortFormat;
run;

proc sort data=summary_tbl out=sorted;
   by group;
   format group $RequiredOrder.;
run;

proc datasets nolist;
   modify sorted;
      format group;
quit;