topic Re: swap rows based on name column in SAS Programming

swap rows based on name column

BrahmanandaRao — Tue, 09 Sep 2025 08:39:37 GMT

Hi Experts

Here I have a question i want swap row based on name variable how to solve if it huge data could you please give solution

data inputs;
input name $ id ;
datalines ;
A 1
B 6
C 3
D 9
E 5
F 6
G 7
;
run;


/*Required output*/

Name id

A 6
B 1
C 9
D 3
E 6
F 5
G 7

Re: swap rows based on name column

PeterClemmensen — Tue, 09 Sep 2025 08:54:05 GMT

I don't understand the logic here. Please be more specific.

Re: swap rows based on name column

BrahmanandaRao — Tue, 09 Sep 2025 09:04:54 GMT

i tried i want swap two id's for each name keep last name id as it is

data inputs;
input name $ id;
datalines;
A 1
B 6
C 3
D 9
E 5
F 6
G 7
H 8
I 9
;
run;

/* Swap IDs using arrays */
data want;
   array n[100] $ _temporary_;   /* to hold names */
   array x[100]   _temporary_;   /* to hold ids   */

   do i=1 by 1 until (lastrow);
      set inputs end=lastrow;
      n[i] = name;
      x[i] = id;
   end;

   /* Swap in pairs */
   do j=1 to i by 2;
      if j+1 <= i then do;
         temp   = x[j];
         x[j]   = x[j+1];
         x[j+1] = temp;
      end;
   end;

   /* Output final dataset */
   do k=1 to i;
      name = n[k];
      id   = x[k];
      output;
   end;

   keep name id;
run;

proc print data=want noobs; run;

Re: swap rows based on name column

Ksharp — Tue, 09 Sep 2025 09:30:01 GMT

data inputs;
input name $ id ;
datalines ;
A 1
B 6
C 3
D 9
E 5
F 6
G 7
;
run;

data want;
 merge inputs inputs(keep=id rename=(id=_id) firstobs=2);
 lag_id=lag(id);
 if mod(_n_,2)=0 then id=lag_id;
  else id=coalesce(_id,id);
 drop _id lag_id;
run;

Re: swap rows based on name column

BrahmanandaRao — Tue, 09 Sep 2025 09:48:21 GMT

Hi Sharp

Thank you

Brilliant Answer can we achieve this in proc sql

Re: swap rows based on name column

Ksharp — Tue, 09 Sep 2025 10:01:53 GMT

Sure. Of course.

data inputs;
input name $ id ;
datalines ;
A 1
B 6
C 3
D 9
E 5
F 6
G 7
;
run;

data temp;
set inputs;
n+1;
run;
proc sql;
create table want as
select name,
case when mod(n,2)=1 then (select id from temp where n=min(a.n+1,(select count(*) from temp))) 
 else (select id from temp where n=a.n-1)
end as id
 from temp as a; 
quit;

Re: swap rows based on name column

BrahmanandaRao — Tue, 09 Sep 2025 10:06:01 GMT

Hi Sharp,

Thank you very much for your proc sql solution i am impressed your solutions👌

Re: swap rows based on name column

Tom — Tue, 09 Sep 2025 13:19:48 GMT

You seem to be missing a variable that would indicate which observations should be pared. And also which member of the pair they are.

You could create one when you read in the data (or afterwords).

data inputs;
  pair = int((_n_+1)/2);
  order= mod(_n_+1,2);
  input name $ id @@ ;
datalines ;
A 1 B 6
C 3 D 9
E 5 F 6
G 7
;

Now it is simple to perform your name swap. Just make a version of the data with the order of the members of the pair reversed and then merge them back together.

proc sort data=inputs out=reverse;
  by pair descending order ;
run;

data want ;
  merge inputs reverse(keep=pair name);
  by pair;
run;

Results