Help using Base SAS procedures

Swapping values in columns and lag function

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Swapping values in columns and lag function

Hello all,

I've been racking my brain on this data manipulation and it is intuitively easy, but I'm having trouble implementing it. I have something like the following table:

ID

EntryExit
101
112
132
205
256
267
278
303
334
354

This is incorrect as Entry should always be LESS THAN Exit and the Exit value for the previous row should be the Entry value for the subsequent row. The following table is correct:

ID

EntryExit
101
112
123
205
256
267
278
303
334
345

I've tried sorting the data by ascending ID and descending entry and exit. Afterwards, I used the lag function. However, I was not getting the bottom table. Any thoughts?

Thank you!

John


Accepted Solutions
Solution
‎08-14-2014 09:25 AM
Super User
Posts: 9,681

Re: Swapping values in columns and lag function

Very interesting question .

data have;
input ID Entry     Exit ;
cards;
1     0     1
1     1     3
1     3     2
2     0     5
2     5     6
2     6     7
2     7     8
3     0     3
3     3     5
3     5     4
;
run;
data temp(drop=entry exit);
 set have;
 value=entry;output;
 value=exit;output;
run;
proc sort data=temp nodupkey;by id value;run;
data x;
 set temp;
 by id;
 if not first.id and not last.id then output;
 output;
run;
data x;
 set x;
 if mod(_n_,2)=1 then do;name='entry'; n+1;end;
  else name='exit';
run;

proc transpose data=x out=want(drop=_: n);
by id n;
id name;
var value;
run;

Xia Keshan

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

Re: Swapping values in columns and lag function

data have;
input ID Entry Exit;
datalines;
1 0 1
1 1 2
1 3 2
2 0 5
2 5 6
2 6 7
2 7 8
3 0 3
3 3 4
3 5 4
;

data want (drop=entry exit rename=(new_entry=entry new_exit=exit));
set have;
if entry>exit then new_entry=coalesce(exit,entry);
else new_entry=entry;
if entry>exit then new_exit=coalesce(entry,exit);
else new_exit=exit;
run;

Contributor
Posts: 27

Re: Swapping values in columns and lag function

Thank you for the quick reply. I made a mistake in my "incorrect" table. It should be:

ID

EntryExit
101
113
132
205
256
267
278
303
335
354
Trusted Advisor
Posts: 1,204

Re: Swapping values in columns and lag function

data want(drop=entry exit rename=(new_entry=entry cnt=exit));

set have;

by id;

if entry>exit then new_entry=coalesce(exit,entry);

else new_entry=entry;

if first.id then cnt=exit;

else cnt+1;

run;

Contributor
Posts: 27

Re: Swapping values in columns and lag function

Thanks for your help thus far. However, my data is a little more general than what I presented. In other words, the values are not all integers that increment by 1. They could have fractions after them. Is there a way to swap two values within a column, in the same way coalesce "swaps" values?

Super User
Super User
Posts: 7,401

Re: Swapping values in columns and lag function

Hi,

What about something like:

proc sql;

     create table WANT as

     select     ID,

                   MIN(ENTRY,EXIT) as ENTRY,

                   MAX(ENTRY,EXIT) as EXIT

     from       HAVE;

quit;

Respected Advisor
Posts: 3,777

Re: Swapping values in columns and lag function

For ID 1 what is "correct" .

If we simply sort ENTRY,EXIT then lag(exit) does not equal entry for obs 2 and 3.

How do you get from here?

ID

EntryExit
101
113
132

to what I guess it still "correct" from your first post?

ID

EntryExit
101
112
123

Do you sort columns first or check lag(exit) eq entry?

Contributor
Posts: 27

Re: Swapping values in columns and lag function

Yes for ID = 1 the bottom table is correct. To be more specific, here is an actual output of my table:

IDEntryExit
4980.000000.12320
4980.123200.25462
4980.254621.78234
4981.782343.20602
4983.206023.15127
5000.000002.00684
5002.006842.16838
5002.168382.19849
5002.198493.04175
5003.041752.54346

You'll notice that for both, ID 498 and 500, the second to last exit values should be flipped with the last exit value. Also for the last row of each ID, the entry and exit should be flipped.

Respected Advisor
Posts: 3,777

Re: Swapping values in columns and lag function

Yes I see, I think.  Looks like as supplied an adequate solution.

Solution
‎08-14-2014 09:25 AM
Super User
Posts: 9,681

Re: Swapping values in columns and lag function

Very interesting question .

data have;
input ID Entry     Exit ;
cards;
1     0     1
1     1     3
1     3     2
2     0     5
2     5     6
2     6     7
2     7     8
3     0     3
3     3     5
3     5     4
;
run;
data temp(drop=entry exit);
 set have;
 value=entry;output;
 value=exit;output;
run;
proc sort data=temp nodupkey;by id value;run;
data x;
 set temp;
 by id;
 if not first.id and not last.id then output;
 output;
run;
data x;
 set x;
 if mod(_n_,2)=1 then do;name='entry'; n+1;end;
  else name='exit';
run;

proc transpose data=x out=want(drop=_: n);
by id n;
id name;
var value;
run;

Xia Keshan

Contributor
Posts: 27

Re: Swapping values in columns and lag function

Thank you very much!

Occasional Contributor
Posts: 18

Re: Swapping values in columns and lag function

Do you want to keep the original Entry value, because it looks like you dont need it, entry starts at zero and is then the previous exit value.

So you could just ignore it:

data have;;
  input id entry exit;

  cards;
498   0.00000  0.12320
498   0.12320  0.25462
498   0.25462  1.78234
498   1.78234  3.20602
498   3.20602  3.15127
500   0.00000  2.00684
500   2.00684  2.16838
500   2.16838  2.19849
500   2.19849  3.04175
500   3.04175  2.54346
;
run;

proc sort data=have;
  by id exit;
run;

data want;
  set have(drop=entry);
  by id;

  retain entry;

  if first.id then
     entry = 0;

  output;

  entry = exit;
run;

Respected Advisor
Posts: 3,124

Re: Swapping values in columns and lag function

I would second @MichaelPearce, and using lag() to make it even less verbose:

data want;

  set have (drop=entry);

  by id;

Entry=ifn(first.id,0,lag(exit));

run;

Haikuo

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 1037 views
  • 3 likes
  • 7 in conversation