Solved
Contributor
Posts: 27

# Swap values using PROC SQL

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?

Accepted Solutions
Solution
‎02-14-2017 01:18 PM
Super Contributor
Posts: 474

## Re: Swap values using PROC SQL

[ Edited ]

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

All Replies
Super User
Posts: 5,694

## Re: Swap values using PROC SQL

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?
Data never sleeps
Contributor
Posts: 27

## Re: Swap values using PROC SQL

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.

Frequent Contributor
Posts: 92

## Re: Swap values using PROC SQL

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.

Super User
Posts: 12,326

## Re: Swap values using PROC SQL

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

Posts: 1,699

## Re: Swap values using PROC SQL

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;

Solution
‎02-14-2017 01:18 PM
Super Contributor
Posts: 474

## Re: Swap values using PROC SQL

[ Edited ]

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

Contributor
Posts: 27

## Re: Swap values using PROC SQL

Thanks Daniel. This works.
Regular Learner
Posts: 1

## Re: Swap values using PROC SQL

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 ;``````
☑ This topic is solved.