Rearranging table for analysis

JohnPura — Tue, 12 Aug 2014 02:04:38 GMT

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

Re: Rearranging table for analysis

Dreamer — Tue, 12 Aug 2014 03:25:42 GMT

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;

topic Rearranging table for analysis in SAS Procedures

Rearranging table for analysis

Re: Rearranging table for analysis