topic Re: Order column in data set by value in last row in SAS Programming

Order column in data set by value in last row

Ronein — Wed, 08 Jan 2020 06:42:47 GMT

Hello

I have a summary table .

The task is to order the columns by the value of the total Row.

The expected order of columns will be :

Team

Model5 (because Total is the lowest)

Model1

Model3

Model2

Model4 (because Total is the highest)

What is the way to do it please?

Data Summary_tbl;
INFILE DATALINES DLM=',';
INPUT Team $  Model1  Model2  Model3  Model4  Model5;
cards;
b,10,18,14,16,4
d,8,24,16,20,6
e,6,18,12,15,8
c,1,7,3,14,2
a,2,1,10,14,1 
Total,27,68,55,79,21
;
Run;

Re: Order column in data set by value in last row

geoskiad — Wed, 08 Jan 2020 08:12:59 GMT

Hi Ronein,

Perhaps there is a more sophisticated solution but what you could do is:

- transpose the values of 'Total' row (only)

- sort this by the values of total (from COL1 by default)

- transpose back what you get from previous step

- concatenate the values from previous step to one variable (e.g with CATX(' ', of MODEL: ) )

- assign the new concatenated value to a macro var (e.g. with CALL SYMPUT)

- use RETAIN statement to define the order of the variables based on the macro var from previous strep (Note: the RETAIN should be before the SET statement)

It should give you what you want.

Re: Order column in data set by value in last row

andreas_lds — Wed, 08 Jan 2020 08:48:22 GMT

Inspired by @geoskiad :

proc transpose data=work.Summary_tbl(where=(Team='Total')) out=work.totals(rename=(_name_=model col1=value));
run;

/* if inside of a macro:
%local VarList;
*/

proc sql noprint;
   select model
      into :VarList separated by ' '
      from work.Totals
      order by value;
quit;

%put &=Varlist;


proc print data=work.summary_tbl;
   var &VarList.;
run;

Re: Order column in data set by value in last row

novinosrin — Thu, 09 Jan 2020 12:50:52 GMT

My share of fun 🙂

Data Summary_tbl;
INFILE DATALINES DLM=',';
INPUT Team $  Model1  Model2  Model3  Model4  Model5;
cards;
b,10,18,14,16,4
d,8,24,16,20,6
e,6,18,12,15,8
c,1,7,3,14,2
a,2,1,10,14,1 
Total,27,68,55,79,21
;
Run;
 
data _null_ ;
 if _n_=1 then do;
  set Summary_tbl(where=(Team='Total'));
  array m(*) model:;
  array t(5);
  call pokelong(peekclong(addrlong(m(1)),40),addrlong(t1),40);
  call sortn(of t(*));
  dcl hash H () ;
  h.definekey("_n_");
  do _n_=1 to dim(t);
   _iorc_=whichn(t(_n_),of m(*));
    h.definedata (vname(m(_iorc_))) ;
  end;
  h.definedone();
  _n_=1;
 end;
 set Summary_tbl end=z;
   h.replace();
 if z;
 h.output(dataset:'want');
run;

Re: Order column in data set by value in last row

geoskiad — Wed, 08 Jan 2020 13:28:05 GMT

I knew my code was not sophisticated 🙂

proc transpose data = summary_tbl out = x;
  where team = 'Total';
run;

proc sort data = x;
  by col1;
run;

proc transpose data = x out = x_t;
  var _name_;
run;

data _null_;
set x_t;
  call symput('xx', catx(' ', of Model: )); 
run;

data Summary_tbl2;
 retain Team &xx.;
set Summary_tbl;
run;

Re: Order column in data set by value in last row

novinosrin — Wed, 08 Jan 2020 13:30:02 GMT

I don't claim mine is either. SAS is a great video game is all I know. 🙂

Re: Order column in data set by value in last row

mkeintz — Wed, 08 Jan 2020 16:58:07 GMT

This is a problem where the ability to dynamically build hash tables (in particular dynamically add data components to the hash definedata method helps. The program below (1) reads in the total row, (2) works from the min to the max of the total row, adding data components to the hash definition in corresponding order, (3) then reads in all the data set, adding each obs to the hash object, and (4) at the end of input ouput the hash object to data set WANT:

Data Summary_tbl;
  INFILE DATALINES DLM=',';
  INPUT Team $  Model1  Model2  Model3  Model4  Model5;
cards;
b,10,18,14,16,4
d,8,24,16,20,6
e,6,18,12,15,8
c,1,7,3,14,2
a,2,1,10,14,1 
Total,27,68,55,79,21
;
Run;

data _null_;
  set summary_tbl (where=(team='Total'))  summary_tbl end=end_of_data;
  array _tot {*} model: ;
  if _n_=1 then do;
    declare hash h (ordered:'a');
    h.definekey('seq');
    h.definedata('team');
    do until (n(of _tot{*})=0 );
      i=whichn(min(of _tot{*}),of _tot{*});
      h.definedata(vname(_tot{i}));
      call missing(_tot{i});
    end;
    h.definedone();
  end;
  seq=_n_;
  if _n_>1 then h.add();
  if end_of_data;
  h.output (dataset:'want');
run;

In the SET statement, the total row is read, followed by all the rows (including the total row at the end of the data set).
You see the h.definedata method statement embedded in a loop over changing minimum totals as per my strategy above.
To preserve the original row order, there is a variable SEQ used as the h object key, and the hash object is declared as sorted.
The "if _n_>1 then h.add()" avoids adding the total row at the beginning of the hash object.

Re: Order column in data set by value in last row

Ronein — Thu, 09 Jan 2020 12:25:28 GMT

Thank you.

Total row is not in your output and I want it too...

Re: Order column in data set by value in last row

novinosrin — Thu, 09 Jan 2020 12:49:14 GMT

HI @Ronein Good catch and thank you pointing me lack of attention to detail as to have missed to include h.definedata("Team");

So, I have added that below in the modified one 🙂 Thanks again

Data Summary_tbl;
INFILE DATALINES DLM=',';
INPUT Team $  Model1  Model2  Model3  Model4  Model5;
cards;
b,10,18,14,16,4
d,8,24,16,20,6
e,6,18,12,15,8
c,1,7,3,14,2
a,2,1,10,14,1 
Total,27,68,55,79,21
;
Run;

data _null_ ;
 if _n_=1 then do;
  set Summary_tbl(where=(Team='Total'));
  array m(*) model:;
  array t(5);
  call pokelong(peekclong(addrlong(m(1)),40),addrlong(t1),40);
  call sortn(of t(*));
  dcl hash H (ordered:'a') ;
  h.definekey("_n_");
  h.definedata("Team");
  do _n_=1 to dim(t);
   _iorc_=whichn(t(_n_),of m(*));
    h.definedata (vname(m(_iorc_))) ;
  end;
  h.definedone();
  _n_=1;
 end;
 set Summary_tbl end=z;
  h.replace();
 if z;
 h.output(dataset:'want');
run;