BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
adityaa9z
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
DanielSantos
Barite | Level 11

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

View solution in original post

8 REPLIES 8
LinusH
Tourmaline | Level 20
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
adityaa9z
Obsidian | Level 7

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
Pyrite | Level 9

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
Super User

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
Garnet | Level 18

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
Barite | Level 11

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
Obsidian | Level 7
Thanks Daniel. This works. 🙂
mohdsheikibrahi
Calcite | Level 5

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 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1987 views
  • 2 likes
  • 7 in conversation