swap rows based on name column

[BrahmanandaRao]

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

[PeterClemmensen]

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

[BrahmanandaRao]

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;

[Ksharp · Accepted solution]

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;

[BrahmanandaRao]

Hi Sharp

Thank you 

Brilliant Answer  can we achieve this in proc sql

[Ksharp]

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;

[BrahmanandaRao]

Hi Sharp,

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

[Tom]

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