topic Re: sort all variables vertically in SAS Programming

sort all variables vertically

pavank — Wed, 08 Jan 2025 05:40:59 GMT

data v_sort;
input v1$ v2$ V3$ ;
cards;
A  B  C
F  K  J
D  E  F
C  K  J
K  L  W
S  V  R
;
run;

how to sort all variables

Re: sort all variables vertically

Kurt_Bremser — Wed, 08 Jan 2025 10:11:32 GMT

What is your intended result?

Re: sort all variables vertically

PaigeMiller — Wed, 08 Jan 2025 11:07:22 GMT

In general, your question to sort all variables doesn't make sense. Once you sort by variable V1, this may make V2 out of sort order, and if you sort by V2, this may make V1 out of sort order.

So, when @Kurt_Bremser asks "What is your intended result?", I also want to know in words what you mean by "sort all variables". Showing us for this extremely small data set may not be sufficient. (And by the way @pavank you should always explain in detail what you want, and give examples of the desired output, in your first post, without us having to ask).

Re: sort all variables vertically

yabwon — Wed, 08 Jan 2025 11:11:06 GMT

Looks like a school homework task to me.

data v_sort;
input v1$ v2$ V3$ ;
cards;
A  B  C
F  K  J
D  E  F
C  K  J
K  L  W
S  V  R
;
run;

proc contents data=v_sort out=list(keep=NAME varnum) noprint;
proc sort data=list;
by varnum;
run;


data _null_;
set list end=_E_;
call execute(cats('proc sort data=v_sort(keep=',name,') out=___',varnum,'; by _all_; run;'));
if _E_ then call execute(cats('data v_sort2; merge ___1-___',varnum,'; run;'));
run;

Re: sort all variables vertically

mkeintz — Sat, 17 May 2025 19:57:15 GMT

I assume that @yabwon 's intepretation of what you want is correct: i.e. that each of the columns V1, V2, and V3 should be independently sorted.

Here is a single-pass of the data that does what you request:

data want;
  set v_sort  end=end_of_data;
  array cols {*} v1-v3;
  if _n_=1 then do;
    declare hash _v1 (ordered:'a');
      _v1.definekey('v1','_n_');
      _v1.definedata('v1');
      _v1.definedone();
    declare hiter _i1 ('_v1');
    declare hash _v2 (ordered:'a');
      _v2.definekey('v2','_n_');
      _v2.definedata('v2');
      _v2.definedone();
    declare hiter _i2 ('_v2');
    declare hash _v3 (ordered:'a');
      _v3.definekey('v3','_n_');
      _v3.definedata('v3');
      _v3.definedone();
    declare hiter _i3 ('_v3');
  end;

  _v1.add();
  _v2.add();
  _v3.add();
  if end_of_data;
  do while(_i1.next()=0 and _i2.next()=0 and _i3.next()=0);
    output;
  end;
run;

The hash keys above include _N_ as a secondary key, to simplify treatment of ties in the primary key.

Of course, this program becomes a lot bigger as the number of columns to be sorted increases. Below is a program whose size is constant regardless of the number of columns to be sorted. But note that, while the columns can be put into an array of increasing size, the hash objects can't be put into an array. Instead, a hash-of-hashes performs that service:

data want (keep=v:);
  set v_sort end=end_of_data;
  array cols{*} v1-v3;
  if _n_=1 then do;
    col=.;
    declare hash h ;  /*Reserve "H" as a hash-object name, not as a varname*/
    declare hiter hi ;
    declare hash hoh ();
      hoh.definekey ('col');
      hoh.definedata('h','hi');
      hoh.definedone();

    do col=1 to dim(cols);
      h=_new_ hash (ordered:'a');  /*"Instantiate" an instance of the H hash object */
        h.definekey(vname(cols{col}),'_n_');
        h.definedata(vname(cols{col}));
        h.definedone();
      hi=_new_ hiter('h');
      hoh.add();
    end;
  end;

  do col=1 to dim(cols);
    hoh.find();  /*Load the column-specific hash*/
    h.add();
  end;

  if end_of_data then do i=1 to _n_;
    do col=1 to dim(cols);
      hoh.find();
      hi.next();
    end;
    output;
  end;
run;

Re: sort all variables vertically

PaigeMiller — Thu, 09 Jan 2025 16:47:34 GMT

@mkeintz wrote:

I assume that @yabwon 's intepretation of what you want is correct: i.e. that each of the columns V1, V2, and V3 should be independently sorted.

I did not assume this, it seems as if Kurt did not assume this, and while you may be correct that this is what the OP wants, you may also be incorrect. Again, I request @pavank provide more explanation about what he wants. We shouldn't have to guess, we shouldn't have to assume, and none of us wants to spend time coming up with a solution that is not what the OP wants.

Re: sort all variables vertically

mkeintz — Thu, 09 Jan 2025 16:47:18 GMT

@PaigeMiller wrote:

@mkeintz wrote:

I assume that @yabwon 's intepretation of what you want is correct: i.e. that each of the columns V1, V2, and V3 should be independently sorted.

I did not assume this, it seems as if Kurt did not assume this, and while you may be correct that this is what the OP wants, you may also be incorrect. Again, I request @pavank provide more explanation about what he wants. We shouldn't have to guess, we shouldn't have to assume.

I agree in that request.

Re: sort all variables vertically

dxiao2017 — Thu, 15 May 2025 19:16:52 GMT

Hi @pavank does this answer your question? (Please omit the proc sql; step, that step was just to see if the v1,v2,v3 tables generated correctly, you do not need the proc sql; step to generate the final output)

data v_sort;
   input v1$ v2$ v3$;
   cards;
A B C
F K J
D E F
C K J
K L W
S V R
;
run;
data v1(keep=v1)
     v2(keep=v2)
     v3(keep=v3);
   set v_sort;
run;
proc sql noprint;
select * from v1;
select * from v2;
select * from v3;
quit;
proc sort data=v1;by v1;run;
proc sort data=v2;by v2;run;
proc sort data=v3;by v3;run;
data final;
   merge v1 v2 v3;
run;
proc print data=final;run;

The repeated data;set; and proc sort; steps can be replaced by a macro, like this (the code, log, and result are as follows): (please omit the proc sql; step, that step was just to see if the tables v1,v2,v3 generated correctly, you do not need the proc sql; step for the final table)

data v_sort;
   input v1$ v2$ v3$;
   cards;
A B C
F K J
D E F
C K J
K L W
S V R
;
run;
%macro sortvars;
%do i=1 %to 3;
data v&i(keep=v&i);
   set v_sort;
run;
proc sql noprint;
select * from v&i;
quit;
proc sort data=v&i;by v&i;run;
%end;
%mend sortvars;
options symbolgen mlogic mprint;
%sortvars;
options nosymbolgen nomlogic nomprint;
data final;
   merge v1 v2 v3;
run;
proc print data=final;run;

 69         data v_sort;
 70            input v1$ v2$ v3$;
 71            cards;
 
 NOTE: The data set WORK.V_SORT has 6 observations and 3 variables.

 78         ;
 79         run;
 80         %macro sortvars;
 81         %do i=1 %to 3;
 82         data v&i(keep=v&i);
 83            set v_sort;
 84         run;
 85         proc sql noprint;
 86         select * from v&i;
 87         quit;
 88         proc sort data=v&i;by v&i;run;
 89         %end;
 90         %mend sortvars;
 91         options symbolgen mlogic mprint;
 92         %sortvars;
 MLOGIC(SORTVARS):  Beginning execution.
 MLOGIC(SORTVARS):  %DO loop beginning; index variable I; start value is 1; stop value is 3; 
by value is 1.  
 SYMBOLGEN:  Macro variable I resolves to 1
 SYMBOLGEN:  Macro variable I resolves to 1
 MPRINT(SORTVARS):   data v1(keep=v1);
 MPRINT(SORTVARS):   set v_sort;
 MPRINT(SORTVARS):   run;
 
 NOTE: There were 6 observations read from the data set WORK.V_SORT.
 NOTE: The data set WORK.V1 has 6 observations and 1 variables.

 MPRINT(SORTVARS):   proc sql noprint;
 SYMBOLGEN:  Macro variable I resolves to 1
 MPRINT(SORTVARS):   select * from v1;
 MPRINT(SORTVARS):   quit;
 NOTE: PROCEDURE SQL used (Total process time):

 SYMBOLGEN:  Macro variable I resolves to 1
 MPRINT(SORTVARS):   proc sort data=v1;
 SYMBOLGEN:  Macro variable I resolves to 1
 MPRINT(SORTVARS):  by v1;
 MPRINT(SORTVARS):  run;
 
 NOTE: There were 6 observations read from the data set WORK.V1.
 NOTE: The data set WORK.V1 has 6 observations and 1 variables.

 MLOGIC(SORTVARS):  %DO loop index variable I is now 2; loop will iterate again.
 SYMBOLGEN:  Macro variable I resolves to 2
 SYMBOLGEN:  Macro variable I resolves to 2
 MPRINT(SORTVARS):   data v2(keep=v2);
 MPRINT(SORTVARS):   set v_sort;
 MPRINT(SORTVARS):   run;
 
 NOTE: There were 6 observations read from the data set WORK.V_SORT.
 NOTE: The data set WORK.V2 has 6 observations and 1 variables.

 MPRINT(SORTVARS):   proc sql noprint;
 SYMBOLGEN:  Macro variable I resolves to 2
 MPRINT(SORTVARS):   select * from v2;
 MPRINT(SORTVARS):   quit;
 NOTE: PROCEDURE SQL used (Total process time):

 SYMBOLGEN:  Macro variable I resolves to 2
 MPRINT(SORTVARS):   proc sort data=v2;
 SYMBOLGEN:  Macro variable I resolves to 2
 MPRINT(SORTVARS):  by v2;
 MPRINT(SORTVARS):  run;
 
 NOTE: There were 6 observations read from the data set WORK.V2.
 NOTE: The data set WORK.V2 has 6 observations and 1 variables.

 MLOGIC(SORTVARS):  %DO loop index variable I is now 3; loop will iterate again.
 SYMBOLGEN:  Macro variable I resolves to 3
 SYMBOLGEN:  Macro variable I resolves to 3
 MPRINT(SORTVARS):   data v3(keep=v3);
 MPRINT(SORTVARS):   set v_sort;
 MPRINT(SORTVARS):   run;
 
 NOTE: There were 6 observations read from the data set WORK.V_SORT.
 NOTE: The data set WORK.V3 has 6 observations and 1 variables.

 MPRINT(SORTVARS):   proc sql noprint;
 SYMBOLGEN:  Macro variable I resolves to 3
 MPRINT(SORTVARS):   select * from v3;
 MPRINT(SORTVARS):   quit;

 SYMBOLGEN:  Macro variable I resolves to 3
 MPRINT(SORTVARS):   proc sort data=v3;
 SYMBOLGEN:  Macro variable I resolves to 3
 MPRINT(SORTVARS):  by v3;
 MPRINT(SORTVARS):  run;
 
 NOTE: There were 6 observations read from the data set WORK.V3.
 NOTE: The data set WORK.V3 has 6 observations and 1 variables.

 MLOGIC(SORTVARS):  %DO loop index variable I is now 4; loop will not iterate again.
 MLOGIC(SORTVARS):  Ending execution.
 93         options nosymbolgen nomlogic nomprint;
 94         data final;
 95            merge v1 v2 v3;
 96         run;
 
 NOTE: There were 6 observations read from the data set WORK.V1.
 NOTE: There were 6 observations read from the data set WORK.V2.
 NOTE: There were 6 observations read from the data set WORK.V3.
 NOTE: The data set WORK.FINAL has 6 observations and 3 variables.