Data Step

[jacksonan123]

I have the following data.

Subject	Time	DV
1	20	10
1	24	5
2	20	10
2	24	15
2	20	20
2	24	30

I would like to do the following:

1.compare DV values at 20 and 24 hrs

2.whenever the DV value at 24 hr is greater than the DV at 20 hr delete the value at 24 hrs. and substitute the value (0.5x DV at 20 hr).

How do I accomplish this task?

[Kurt_Bremser]

Use the lag() function to retrieve the value of a variable in the previous observation.

[jacksonan123]

Ok, I will write some code using the lag function to resolve the issue and
if successful, I will post the results.

[PGStats]

What happens when there are multiple values at time=20 for a given subject? Which one should be used in the calculation of (0.5x DV at 20 hr)?

[jacksonan123]

For each subject there will be only one value at 20 hr. and at 24 hr.

[jacksonan123]

data new;
input subject time dv;
datalines;
1 20 10 
1 24 5
2 20 10 
2 24 15
3 20 10
3 24 20
;
run;


data old;
set new;
retain subject dv lagdv x;
do subject=1 to 3;
if time in (20 24);
lagdv=lag(dv);
if dv>lag(dv) then x=0.5*lag(dv);
end;
output;
keep subject dv x;
run;

I ran this code and the result had an issue with outputting the correct subject's number.  Can someone correct the code for me so that subjects 1-3 will be output? 

Obs subject dv x123456

4	10	.
4	5	.
4	10	5.0
4	15	5.0
4	10	5.0
4	20	7.5

[PGStats]

SQL will be more robust to unforeseen cases than a data step. Try this:

data test;
input Subject Time DV;
datalines;
1 20 10
1 24 5
2 20 10
2 24 15
3 20 20
3 24 30
;

proc sql;

create table test20 as
select * from test where time=20;

update test as a
set DV = (select mean(0.5*DV) from test20 where subject=a.subject)
where time = 24 and DV > (select mean(DV) from test20 where subject=a.subject);

drop table test20;

quit;

[jacksonan123]

The resulting Table20 did not have values for 24 hr.  Where can I find that table?

Obs Subject Time DV123

1	20	10
2	20	10
3	20	20

[PGStats · Accepted solution]

Run the test that I provided and you will see that table test gets updated with the desired values.