topic Ordering with respect to a custom order in SAS Enterprise Guide

Ordering with respect to a custom order

sleretrano — Mon, 16 Jan 2017 13:03:33 GMT

I'm using SAS EG 5.1 on Windows 7.

I have the following sample input.

I wish to order this input in such a way that whenever different observations match on ID and on Time, the final state of a given observation matches the initial state of the next observation.

The output should look like this:

I'm at a loss on how to do this. Any help?

Here's the code that generates the input table.

Data Test;
   Infile datalines delimiter=','; 
   Input ID Initial_Stafe $ Final_State $ Time;
   Datalines;
   123,AA,BB,201701020923
   123,BB,CC,201701020930
   156,AA,AA,201701020910
   163,AC,BC,201701031010
   163,DD,CC,201701031010
   163,BC,DD,201701031010
   163,CC,BB,201701031010
   170,BB,.,201701040930
;

Re: Ordering with respect to a custom order

LinusH — Mon, 16 Jan 2017 14:28:25 GMT

Assuming that state has logical (and unbreakable) path.

Also assuming that state is global, the order is the same independent of id, or...?

If so, create a lookup table with a your state and a numerical order by variable, merge/join to get this order by variable, and then resort.

Re: Ordering with respect to a custom order

sleretrano — Mon, 16 Jan 2017 14:44:04 GMT

Thank you for your time.
Yes, assume that there always is a "proper" path.

I'm not sure I understand your question, The ordering, by descending priority is: ID, Time, Path.

Can you please provide the code to your suggestion?

Re: Ordering with respect to a custom order

LinusH — Mon, 16 Jan 2017 16:12:05 GMT

Nothing fancy just like:
Create table want as
Select have.*
From have
Inner join state_lookup
On have.initial_state = state_lookup.state
Order by state_lookup.state_order
;