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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

12 REPLIES 12
stat_sas
Ammonite | Level 13

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;

JohnPura
Calcite | Level 5

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
stat_sas
Ammonite | Level 13

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;

JohnPura
Calcite | Level 5

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

data_null__
Jade | Level 19

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?

JohnPura
Calcite | Level 5

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.

data_null__
Jade | Level 19

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

Ksharp
Super User

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

JohnPura
Calcite | Level 5

Thank you very much!

MichaelPearce
Obsidian | Level 7

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;

Haikuo
Onyx | Level 15

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 12 replies
  • 3401 views
  • 3 likes
  • 7 in conversation