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
;

 

Super User
Posts: 5,829

Re: Ordering with respect to a custom order

Posted in reply to sleretrano

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?
Super User
Posts: 5,829

Re: Ordering with respect to a custom order

Posted in reply to sleretrano
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
  • 186 views
  • 0 likes
  • 2 in conversation