Getting values of a variable from previous set of observations

[A_B_C]

patid week day dose
1 1 1 10
1 1 3 11
1 2 1 12
1 2 3 13
1 3 1 13
1 4 1 14
1 5 1 15
1 5 3 16
1 6 1 17
1 6 3 18
At any iteration of datastep, at day1 I want to get dose values of previous two weeks.
For example at week5 day1 I should be able to access values of dose for weeks 3 and 4. for week6 I should be able to get dose values of week5 and week4.
how can I accomplish this. help me.

[GertNissen]

Have a look at the LAG function - lag1(), lag2()
http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000212547.htm

[BPD]

A_B_C,

You could read the data from the prior 2 weeks using multiple set statements.
VIZ:
data a;
input patid week day dose;
Infile cards;
cards;
1 1 1 10
1 1 3 11
1 2 1 12
1 2 3 13
1 3 1 13
1 4 1 14
1 5 1 15
1 5 3 16
1 6 1 17
1 6 3 18
;
run;

data b;
set a;
if _n_ > 1 then set a(keep = dose rename=(dose=dosemin1));
if _n_ > 2 then set a(keep = dose rename=(dose=dosemin2));
run;

proc print data=b;
run;

This produces the output shown below. Once enough data is present you simply look along the observation to look-up the last and last-1 positions.

Obs patid week day dose dosemin1 dosemin2

1 1 1 1 10 . .
2 1 1 3 11 10 .
3 1 2 1 12 11 10
4 1 2 3 13 12 11
5 1 3 1 13 13 12
6 1 4 1 14 13 13
7 1 5 1 15 14 13
8 1 5 3 16 15 14
9 1 6 1 17 16 15
10 1 6 3 18 17 16

Regards,

BPD

[A_B_C]

Hi BPD,
This is fine. what if I need to get previous 4 weeks data. If I use the same logic, it creates more variables. I just want to know is there any other way that can be used to solve this problem? or is it the only way to do it. I dont think use of lag function is a right choice as there will be missing dose at some weeks.

[edilts]

It would be useful to know exactly what you are trying to do - saying you need to "access" is unclear. That said, this code does what I think you are asking for, and it will work with any number of weeks.


* Create week totals for each patid;
proc sql;
create table test2 as select distinct patid,week,sum(dose) as WeekTtl
from test group by patid,week order by patid,week;
quit;

* Flip long to wide to get values from every week onto one row;
proc transpose data=test2 out=test2t(drop=_NAME_) prefix=Week;
by patid;
id week;
var WeekTtl;
run;

* Merge transposed data back with original data by patid;
data test3;
merge test test2t;
by patid;
run;

===============

if you do not want to use sql, this duplicates that step.

proc sort data=test out=test4;
by patid week day;
run;

data test4(keep=patid week WeekTtl);
retain WeekTtl;
set test4;
by patid week day;
if first.week then WeekTtl=dose;
else WeekTtl=WeekTtl+dose;
if last.week then output;
run;

ED

[Peter_C]

by-group on patient, collecting dosage in an array by week/day with as many entries as your data history would need for a patient.
At last row for patient, analyse over week/day: For a patient, on any day with history, examine the array up to that day, to determine whatever you need.
If you need one year of history, define the array for 52 weeks and 7 days like[pre] array dose_hist( 52,7 ) ; [/pre] accessed like [pre] dose_hist( week,day ) = dosage ; [/pre]

PeterC

[BPD]

A_B_C,

I hadn't realised from your original post that you wanted that flexibility.

It's a simple step to put the code you require into a macro. For example:

data a;
input patid week day dose;
Infile cards;
cards;
1 1 1 10
1 1 3 11
1 2 1 12
1 2 3 13
1 3 1 13
1 4 1 14
1 5 1 15
1 5 3 16
1 6 1 17
1 6 3 18
;
run;

/* Now define the macro - note that timst and timend allow you to name the
start and stop months that you're interested in. */

%MACRO reread(track=,timst=,timend=,in=a,out=b);
DATA &out;
SET ∈
%DO i = &timst %TO %EVAL(&timend);
IF _n_ > &i THEN SET &in(KEEP = &track RENAME=(&track=&track.min&i));
%END;
RUN;
%MEND reread;

/* now - to track the variable dose for 2 to 3 months ago */

%reread(track=dose,timst=2,timend=3,in=a,out=b);

proc print data=b;
run;

/* the above print gives the following output. */

Obs patid week day dose dosemin2 dosemin3

1 1 1 1 10 . .
2 1 1 3 11 . .
3 1 2 1 12 10 .
4 1 2 3 13 11 10
5 1 3 1 13 12 11
6 1 4 1 14 13 12
7 1 5 1 15 13 13
8 1 5 3 16 14 13
9 1 6 1 17 15 14
10 1 6 3 18 16 15


Perhaps this is addresses your needs more accurately?

Regards,

BPD

[A_B_C]

this looks fine. But if you look at week5 day1, it is not giving the previous week week4 value rather its giving values of week3 only. Another thing is that, again it is difficult to know which dose column represents which particular week.
For example at wee5 day1 it is difficult to know what are the dose columns that I need to use to get the values, might be simple for 2 weeks but if I want to use 4 weeks, it seems more tricky

[Peter_C]

that's why I suggest a patient array by week,day.
You are able to review multiple weeks together

[A_B_C]

I tried out as you suggested, but still not able to get values previous week exactly.
here goes the code and output.
data a;
input patid week day dose;
Infile cards;
cards;
1 1 1 10
1 1 3 11
1 2 1 12
1 2 3 13
1 3 1 13
1 4 1 14
1 5 1 15
1 5 3 16
1 6 1 17
1 6 3 18
;
run;
data b;
set a;
by patid;
array a(6,3);
a(week,day)=dose;
run;
proc print;run;

data a;
input patid week day dose;
Infile cards;
cards;
1 1 1 10
1 1 3 11
1 2 1 12
1 2 3 13
1 3 1 13
1 4 1 14
1 5 1 15
1 5 3 16
1 6 1 17
1 6 3 18
;
run;
data b;
set a;
by patid;
array a(6,3);
a(week,day)=dose;
run;
proc print;run;
data a;
input patid week day dose;
Infile cards;
cards;
1 1 1 10
1 1 3 11
1 2 1 12
1 2 3 13
1 3 1 13
1 4 1 14
1 5 1 15
1 5 3 16
1 6 1 17
1 6 3 18
;
run;
data b;
set a;
by patid;
array a(6,3);
a(week,day)=dose;
run;
proc print;run;
Obs
patid week day dose a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a13 a14 a15 a16 a17 a18
1 1 1 1 10 10 . . . . . . . . . . . . . . . . .
2 1 1 3 11 . . 11 . . . . . . . . . . . . . . .
3 1 2 1 12 . . . 12 . . . . . . . . . . . . . .
4 1 2 3 13 . . . . . 13 . . . . . . . . . . . .
5 1 3 1 13 . . . . . . 13 . . . . . . . . . . .
6 1 4 1 14 . . . . . . . . . 14 . . . . . . . .
7 1 5 1 15 . . . . . . . . . . . . 15 . . . . .
8 1 5 3 16 . . . . . . . . . . . . . . 16 . . .
9 1 6 1 17 . . . . . . . . . . . . . . . 17 . .
10 1 6 3 18 . . . . . . . . . . . . . . . . . 18

As this output contains missing columns of array, I think it is difficult to find which column value represents which week/day value. For example at week5 day1, still I am not able use week 4 value. please let me know if I need to any other changes to above code to resolve my issue