topic Re: swap obs first and last each other in SAS Programming

swap obs first and last each other

pavank — Fri, 22 Dec 2023 07:20:28 GMT

data testy;
     input a; 
     datalines;
1
2
3
4
5
6
7
8
9
;
run;
/*how to swap first obs and last obs each other in SAS*/

required output
9
2
3
4
5
6
7
8
1

Question: How to swap values each other as above required output

Re: swap obs first and last each other

andreas_lds — Fri, 22 Dec 2023 07:42:14 GMT

Interesting task.

Maybe something like:

data _null_;
   call symputx('lastObs', _nobs);
   call symputx('beforeLast', _nobs -1);
   if 0 then set work.have nobs=_nobs;
run;

data want;
   set 
      work.have(firstobs=&lastObs.)
      work.have(firstobs=2 obs=&beforeLast.)
      work.have(firstobs=1 obs=1)
   ;
run;

Re: swap obs first and last each other

whymath — Fri, 22 Dec 2023 07:52:38 GMT

If you want to swap two specific rows, like the ith and the jth row, obs= and firstobs= options would be very efficiently.

data want;
  set testy(firstobs=9 obs=9) testy(firstobs=2 obs=8) testy(firstobs=1 obs=1);
run;

If you want to swap two variant rows, like the first and last row, or some rows which be determined by a variable, you may need direct access method.

data want;
  set testy nobs=nobs end=eof;
  if _n_=1 then set testy point=nobs;
  if eof then set testy point=eof;
run;

Re: swap obs first and last each other

pavank — Fri, 22 Dec 2023 08:24:45 GMT

Hi Whymath

Thank you for your solution

supposer we want swap range of values how to achieve

example required output

9
8
7
4
5
6
3
2
1

Re: swap obs first and last each other

Kurt_Bremser — Fri, 22 Dec 2023 08:35:09 GMT

What is the exact rule for this?

Re: swap obs first and last each other

s_lassen — Fri, 22 Dec 2023 12:30:09 GMT

You can try something like this for the last wanted result you show:

data want;
  do _N_=nobs to nobs-2 by -1, 4 to nobs-3, 3 to 1 by -1;
    set have point=_N_ nobs=nobs;
    output;
   end;
  stop;
run;

Your original result with the same style of coding:

data want;
  do _N_=nobs, 2 to nobs-1, 1;
    set have point=_N_ nobs=nobs;
    output;
   end;
  stop;
run;

Re: swap obs first and last each other

ballardw — Fri, 22 Dec 2023 15:56:35 GMT

Let us broaden the question a bit. WHY exactly is the order of values of a single variable important? As in, what impact does it have on analysis or reporting?

An abstract example like this is quite often amenable to a solution but then when you try to expand its use, such as to groups of values within an analysis group, think geographic region or demographic group, it becomes quite complex. But may not actually be needed depending on your actual use need.

Re: swap obs first and last each other

mkeintz — Sat, 23 Dec 2023 03:06:07 GMT

data want;
  if _n_=1 then set have nobs=n_last point=n_last;
  else set have (firstobs=2) end=end_of_have;
  if end_of_have then set have (obs=1);
run;

data want;
  if _n_=1 then set sashelp.class nobs=n_last point=n_last;
  else set sashelp.class (firstobs=2)  sashelp.class (obs=1);
run;

Re: swap obs first and last each other

mkeintz — Sat, 23 Dec 2023 01:03:01 GMT

@pavank wrote:

Hi Whymath

Thank you for your solution

supposer we want swap range of values how to achieve

example required output

9
8
7
4
5
6
3
2
1

You can (edited response):

data have;
  do i=1 to 9; output; end;
run;

%let groupsize_beg=3;
%let groupsize_end=3;

data want;
  do ptr= n_last to n_last +1 -&groupsize_end by -1
        , &groupsize_beg+1 to n_last-&groupsize_end
        , &groupsize_beg to 1 by -1;
    set have point=ptr nobs=n_last;
    output;
  end;
  stop;
run;

or you can make it more compact via:

data want;
  ptr=ifn(_n_=1,n_last,ptr-1);
  if ptr=0 then stop;
  /*Read end group and begin group in reverse order, using "POINT=" */
  if _n_<=&groupsize_end  or  _n_> n_last-&groupsize_beg  then set have point=ptr nobs=n_last;
  /*Otherwise read middle group in original order */
  else set have (firstobs=%eval(&groupsize_beg+1));

run;

The below is stricken out, once I realized you want the last batch of records in reverse order.

data have;
  do i=1 to 9; output; end;
run;

%let groupsize_beg=3;
%let groupsize_end=3;

data want;
  do ptr= n_last to n_last +1 -&groupsize_end by -1
        , &groupsize_beg+1 to n_last-&groupsize_end
        , 1 to &groupsize_beg;
    set have point=ptr nobs=n_last;
    output;
  end;
  stop;
run;

~~or slightly more compact, No STOP statement is required because the second and third SETs do not use the POINT= option:~~

data want;
  ptr = n_last + 1 - _n_;

  if _n_<=&groupsize_end              then set have point=ptr nobs=n_last;
  else if _n_<= n_last-&groupsize_beg then set have (firstobs=%eval(&groupsize_beg+1));
  else                                     set have (obs=&groupsize_beg);
run;

Re: swap obs first and last each other

Patrick — Sat, 23 Dec 2023 09:32:20 GMT

You could consider a format to create a grouping for values. Many SAS Procs can return results ordered by formatted values without the need to pre-sort the input data.

data have;
  input row_id;
  datalines;
1
2
3
4
5
6
7
8
9
;

proc format;
  value ValGroup
  7-9 = 1
  1-6 = 2
  other = 3
  ;
run;

proc sql;
/*  create table want as*/
  select row_id
  from have
  order by put(row_id,ValGroup.), row_id
  ;
quit;

Re: swap obs first and last each other

dxiao2017 — Thu, 15 May 2025 18:04:39 GMT

Hi @pavank does this answer your question?

data testy;
   input a;
   datalines;
1
2
3
4
5
6
7
8
9
;
run;
proc print data=testy;run;
proc sql noprint;
select distinct a
   into :avalue1-
   from testy;
quit;
%put &avalue1 &avalue9;
data testy2;
   set testy;
   if _n_=1 then a=&avalue9;
   if _n_=9 then a=&avalue1;
run;
proc print data=testy2;run;