Swap values using PROC SQL

[adityaa9z]

I have a dataset say "A" and I want to swap each value of "A" using proc sql. 

data a;
input x$;
cards;
a
b
a
b
a
b
;
run;

So I would need output as 

b

a

b

a

b

a

I dont want to use any function here. Inputs anyone?

[LinusH]

I love (sarcasm) when the poster "I don't want to use X". What if X is the best solution?
And then I wonder about the requirement. What kind of data is this, no unique id.? What's the purpose?

[adityaa9z]

Haha. I understand your problem. though this was the question asked in one of my interviews. I responded that I can do that with functions. they denied.

Yeah this data doesnt have any unique id. I just want to swap every value with the one below it. Hope this makes sense.

[Sven111]

What do you mean by function?  SAS specific functions?  My first thought would be that you could do this with a CASE statement if that is allowable and you knew all of the swaps that need to happen ahead of time.  I don't think you could solve this with just SELECTs and JOINs, although I'd love to be proved wrong.

[ballardw]

No functions and Proc sql:

proc sql;
   create table want as 
   select *
   from a(firstobs=2) union all (select * from a(firstobs=1 obs=1))
   ;
quit;

would not work for more than two values or data that wasn't specifically ordered as the example.

Could also use a custom format with something like select put(x,myfmt.) as x

[Shmuel]

Next code will do it:

proc sql;

    create table B as select

    case when x="A" then "B"

             when x="B" then "A"
   end as x

from A;

quit;

[DanielSantos · Accepted solution]

Hi.

That's an awkward problem 🙂

%let pairs=2;
proc sql noprint;
create table want as
select monotonic() as pos, x from a
order by (int((calculated pos-1)/&PAIRS)*&PAIRS)+mod(calculated pos,&PAIRS);
quit;

If I got this right, one approach would be to number the observations and then do some math over that to reorder the pairs

int(pos-1)/&PAIRS + mod(pos,&PAIRS)

First part caculates the base value (if PAIRS=2, then 0, 2, 4, 6, ...) 
Second part creates a rotating inde (if PAIRS=2, then +1, +0, +1, +0, ...)

You can drop the calculated pos variable if you want, I just left it there for understanding.

Hope it helps.

Daniel Santos @ www.cgd.pt

[adityaa9z]

Thanks Daniel. This works. 🙂

[mohdsheikibrahi]

This code doesn't work if there is a consecutive occurrence of same value in the list.

like 

x

a

a

b

b

a

b

a

b

Simple case statement in proc sql query will fix it. 

proc sql;
create table want as 
select case x when 'a' then 'b'
				when 'b' then 'a'
				end as x
	from a ;