topic Re: Data Step in SAS Programming

Data Step

jacksonan123 — Wed, 25 Sep 2019 17:36:54 GMT

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?

Re: Data Step

Kurt_Bremser — Wed, 25 Sep 2019 17:47:27 GMT

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

Re: Data Step

jacksonan123 — Wed, 25 Sep 2019 17:53:32 GMT

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

Re: Data Step

PGStats — Wed, 25 Sep 2019 18:12:17 GMT

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

Re: Data Step

jacksonan123 — Wed, 25 Sep 2019 18:14:32 GMT

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

Re: Data Step

jacksonan123 — Wed, 25 Sep 2019 18:39:32 GMT

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

Re: Data Step

PGStats — Wed, 25 Sep 2019 18:47:22 GMT

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;

Re: Data Step

jacksonan123 — Wed, 25 Sep 2019 18:57:29 GMT

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

Re: Data Step

PGStats — Wed, 25 Sep 2019 19:28:13 GMT

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