Hello SAS Users,
I'd like some help setting up a table for analysis. Currently the table looks like the one immediately below:
Each ID is associated with an ENTRY and EXIT time, which are bounded by LEFT and RIGHT limits. The ID's status will vary in each interval. Now, I'd like to arrange the data so that FOR EACH ID, I only have one set of unique left and right values. For instance, for ID = B, the first three lines have left = 0 and right = 0.5. I don't really care about the subintervals between the left and right limits of 0 and 0.5. However, for the last listing of left = 0 and right = 0.5, I want to preserve the value of the STATUS variable. For the 4th line (ID = A, Status = 1, Entry = 0.5, and Exit = 0.67077), I want to keep it as is because the EXIT value never reached the RIGHT value. I've created a table below the first to show what I would like to achieve.
ID | Status | Entry | Exit | Left | Right |
A | 0 | 0.00000 | 0.03559 | 0.0 | 0.5 |
A | 0 | 0.03559 | 0.12868 | 0.0 | 0.5 |
A | 1 | 0.12868 | 0.50000 | 0.0 | 0.5 |
A | 1 | 0.50000 | 0.67077 | 0.5 | 1.0 |
B | 1 | 0.00000 | 0.10678 | 0.0 | 0.5 |
B | 1 | 0.10678 | 0.50000 | 0.0 | 0.5 |
B | 1 | 0.50000 | 1.00000 | 0.5 | 1.0 |
B | 1 | 1.00000 | 1.50000 | 1.0 | 1.5 |
B | 1 | 1.50000 | 1.99589 | 1.5 | 2.0 |
B | 1 | 1.99589 | 2.00000 | 1.5 | 2.0 |
B | 1 | 2.00000 | 2.11910 | 2.0 | 2.5 |
B | 1 | 2.11910 | 2.49418 | 2.0 | 2.5 |
This is the table I want:
ID | Status | Entry | Exit | Left | Right |
A | 1 | 0 | 0.50000 | 0.0 | 0.5 |
A | 1 | 0.50000 | 0.67077 | 0.5 | 1.0 |
B | 1 | 0 | 0.50000 | 0.0 | 0.5 |
B | 1 | 0.50000 | 1.00000 | 0.5 | 1.0 |
B | 1 | 1.00000 | 1.50000 | 1.0 | 1.5 |
B | 1 | 1.5 | 2.00000 | 1.5 | 2.0 |
B | 1 | 2 | 2.49418 | 2.0 | 2.5 |
Thanks for your help!
John
proc sort data=input;by id left right; run;
data want;
set input;
by id left right;
retain temp_entry;
if first.left then temp_entry=entry;
if last.left and last.right then do;
entry=temp_entry;
output;
end;
drop temp_entry;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.