Lapis Lazuli | Level 10

## Data Step

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## Re: Data Step

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

PG
8 REPLIES 8
Super User

## Re: Data Step

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

Lapis Lazuli | Level 10

## Re: Data Step

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

## Re: Data Step

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)?

PG
Lapis Lazuli | Level 10

## Re: Data Step

For each subject there will be only one value at 20 hr. and at 24 hr.
Lapis Lazuli | Level 10

## Re: Data Step

``````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
Opal | Level 21

## Re: Data Step

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;``````
PG
Lapis Lazuli | Level 10

## Re: Data Step

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
Opal | Level 21

## Re: Data Step

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

PG
Discussion stats
• 8 replies
• 911 views
• 0 likes
• 3 in conversation