turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Swap values using PROC SQL

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-14-2017 11:10 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adityaa9z

02-14-2017 12:35 PM - edited 02-14-2017 12:35 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adityaa9z

02-14-2017 11:47 AM

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?

And then I wonder about the requirement. What kind of data is this, no unique id.? What's the purpose?

Data never sleeps

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to LinusH

02-14-2017 11:50 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adityaa9z

02-14-2017 11:56 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adityaa9z

02-14-2017 12:03 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

02-14-2017 12:16 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to adityaa9z

02-14-2017 12:35 PM - edited 02-14-2017 12:35 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DanielSantos

02-14-2017 01:19 PM

Thanks Daniel. This works.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DanielSantos

02-08-2018 10:02 AM

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 ;
```