Rob,

Does this do what you want? (if you want to include week zero changr LT to LE in 2 spots) (Untested). PS. If you know that the sweek values are always ascending you can make this more efficient by stopping when you reach zero.

data temp; set mergefil.merged_ape1; by client_Sin;

array sweek(100) sweek1-sweek100; array fam(100) fam1-fam100;

ii = 1; do i = 2 to 100; if sweek(i) gt sweek(ii) and sweek(i) lt 0 then ii = i;; end; if sweek(ii) lt 0 then do; baseweek = sweek(ii); basefam = fam(ii); end; run;

HTH, Dennis Diskin

Rob <sasuser00@HOTMAIL.COM> wrote: Ok I have figured out how I am going to do this but I need a little help with the code:

Here is a sample of my dataset

ape start date w1 w2 w3 w4 fs1 fs2 fs3 fs4 13156 1 2 -8 -120 11 11 12 11

So w1-w4 are the weeks prior to the ape start date. fs1-fs4 are family status values. What I need is to get the most recent w value prior to the ape start date and then insert the corresponding fs value into my table.

So the most recent prior to the start date is w3 since it is 8 weeks prior to the start date, this means that I want the value of fs3 to go into my control variable c_family_Status.

Here is some code that I have to figure out the minsweek but it's not quite working properly.

data temp; set mergefil.merged_ape1; by client_Sin;

array sweek(100) sweek1-sweek100; array fam(100) fam1-fam100;

minsweek = sweek (-1);

do i = 1 to 100; if sweek(i) > 0 then if sweek(i) < minsweek then minsweek = sweek(i); end; run;

Any suggestions?

On Mon, 18 Oct 2004 11:05:50 -0700, Dennis Diskin wrote:

>Rob, > >Please post a sample layout of your dataset. I find myself having to make too many assumptions as to what the data looks like. For instance, I have to assume that there is also an array of family_status variables. > >Regards, >Dennis Diskin > >Rob wrote: >The problem is that there is an array for date of data. For one record >there is an APE start date and date_of_data1 to date_of_data100. I have >to read each one of these and determine which one is the closest one prior >to the ape start date. Can I add an array to this code? > >The other thing is that I have variables sweek1 to sweek100 and this >computes the time between the two dates. Can I somehow just read through >these variables and retain the lowest value, then I could get the family >status associated with the record and plug it into the control variable. > >I would like to do something like the following: > >if sweek2 GT sweek 1 /* keep the value of sweek 2 */ >then c_family_status = family_status >else if sweek3 gt sweek2 >then c_family_Status = family_status >else if sweek4 gt sweek 3 >etc. > >Any suggestions? > >On Mon, 18 Oct 2004 08:42:41 -0700, Dennis Diskin wrote: > >>A pretty standard problem with various solutions. >>One is to split out a file with the last prior records and then merge it >back in: >>Here's some untested code, assuming yourdata already sorted on sin and >ape_date as in your sample: >> >>data base (keep=sin base_status); >>set yourdata(where=(ape_date LT data_date rename=status=base_status)); >>by sin descending ape_date; >>if first.id; >>run; >> >>data new; >>merge yourdata base; >>by sin; >>run; >> >>HTH, >>Dennis Diskin >> >>Rob wrote: >>Hi, >> >>I am wondering how to do the following: >> >>I am creating some control variables, one is called c_family_status which >>is going to be the status of the most recent record prior to the start >>date. >> >>So for example, I have an APE start date for 5 observations for SIN >>MT0000035. I also have another date (date_of_data) which is in an array >>for the same SIN in the same observation. I have to make sure I get the >>observation with the most recent APE date and which has a date_of_data >>prior to that start date. >> >>For example: >> >>MT35 >> >>APE date of family weeks prior >>date data status to ape date >> >>13608 13758 11 22 >>13660 13758 11 14 >>13782 13758 12 -3 >>13847 13758 11 -12 >>14979 13209 11 -174 >> >>So I would like to see 3 go in to the control variable family status since >>it is the closest date prior to the APE date. >> >>Any help would be appreciated! >> >>Thanks!