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?
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
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.
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.
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
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;
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
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 ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.