Swap values using PROC SQL

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

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 Smiley Happy

 

%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

View solution in original post


All Replies
Super User
Posts: 5,260

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: 82

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: 10,538

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

Trusted Advisor
Posts: 1,400

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 Smiley Happy

 

%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. Smiley Happy
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 205 views
  • 2 likes
  • 6 in conversation