Help using Base SAS procedures

Rearranging table for analysis

Reply
Contributor
Posts: 27

Rearranging table for analysis

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

StatusEntryExitLeftRight
A00.000000.035590.00.5
A00.035590.128680.00.5
A10.128680.500000.00.5
A10.500000.670770.51.0
B10.000000.106780.00.5
B10.106780.500000.00.5
B10.500001.000000.51.0
B11.000001.500001.01.5
B11.500001.995891.52.0
B11.995892.000001.52.0
B12.000002.119102.02.5
B12.119102.494182.02.5

This is the table I want:

IDStatusEntryExitLeftRight
A100.500000.00.5
A10.500000.670770.51.0
B100.500000.00.5
B10.500001.000000.51.0
B11.000001.500001.01.5
B11.52.000001.52.0
B122.494182.02.5

Thanks for your help!

John

Frequent Contributor
Posts: 98

Re: Rearranging table for analysis

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;

Ask a Question
Discussion stats
  • 1 reply
  • 182 views
  • 3 likes
  • 2 in conversation