topic Re: Swap values using PROC SQL in SAS Programming

Swap values using PROC SQL

adityaa9z — Tue, 14 Feb 2017 16:10:35 GMT

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

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

Re: Swap values using PROC SQL

LinusH — Tue, 14 Feb 2017 16:47:04 GMT

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?

Re: Swap values using PROC SQL

adityaa9z — Tue, 14 Feb 2017 16:50:49 GMT

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.

Re: Swap values using PROC SQL

Sven111 — Tue, 14 Feb 2017 16:56:35 GMT

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.

Re: Swap values using PROC SQL

ballardw — Tue, 14 Feb 2017 17:03:34 GMT

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

Re: Swap values using PROC SQL

Shmuel — Tue, 14 Feb 2017 17:16:37 GMT

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;

Re: Swap values using PROC SQL

DanielSantos — Tue, 14 Feb 2017 17:35:59 GMT

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

Re: Swap values using PROC SQL

adityaa9z — Tue, 14 Feb 2017 18:19:05 GMT

Thanks Daniel. This works. 🙂

Re: Swap values using PROC SQL

mohdsheikibrahi — Thu, 08 Feb 2018 15:02:28 GMT

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

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 ;