Desktop productivity for business analysts and programmers

Ordering with respect to a custom order

Reply
Contributor
Posts: 46

Ordering with respect to a custom order

[ Edited ]

I'm using SAS EG 5.1 on Windows 7.

 

I have the following sample input.

Input.jpg

 

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:

Output.jpg

 

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
;

 

Esteemed Advisor
Posts: 5,198

Re: Ordering with respect to a custom order

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.

Data never sleeps
Contributor
Posts: 46

Re: Ordering with respect to a custom order

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?
Esteemed Advisor
Posts: 5,198

Re: Ordering with respect to a custom order

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
;
Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 144 views
  • 0 likes
  • 2 in conversation