Data Step- populate data from prior time to current time

[jacksonan123]

I have a data set new.csv attached which has times 4.5, 7.5, 9, and 10.5 for which the variables DV, amt, evid, wt , and sex are missing.

These values for subjects 1 and 2 are located at the preceding times (i.e., 4, 6, 8 and 10).  I need to populate the .4.5, 7.5, 9, and 10.5 time points with the data from the preceding time point.   For example what I want  for subject 1 at 4.5 hours is to populate all of the 24 cmts at 4.5 hrs with dv=0, amt=0 , evid=2  , wt =23, and sex =1 which are the values just preceding the 4.5 time.  I need this done for the 7.5, 9, and 10.5 time points for each subject.  I actually have 15 subjects but are showing only 2

[ballardw]

First you should provide the code you used to turn that CSV into a data step. I really don't like guessing as to which variables are numeric and which character. Some approaches require knowing the variable types.

Something like this may work. One variable, you do the rest.

data want;
   set have;
   by id time notsorted;
   retail lastdv;
   if last.time then do;
       lastdv=dv;
   end;
   if missing(dv) then dv=lastdv;
   /*drop lastdv;*/
run;

The data assumes that the data is at least grouped by id and time value (the time really should be in order).

When you use a BY statement then automatic variables First.<variable> and Last.<variable> for each variable on the by statement. These are numeric 1/0 and SAS treats 1 as true and 0 as false. So you can test if a specific observation is from the first or last of a group for doing conditional operations. In this case when a Last time is encountered the value is stored in a holding variable.

Retain creates variables whose values are maintained across the data step boundary so are available for use with other observations. This is one place that knowing type of the variable is important as the code I use above creates a numeric value. If the value is character you need to indicate the length to store.

The code above only replaces a missing value if the DV with the retained value.

After you are sure that things are working then you can drop the LastDv (or other variables) if not wanted. I typically test code on a subset of the data and leave things like this in to see where a problem may have crept in.

[Tom]

I really cannot tell from your data how it is organized.

Please post data as a dataset step and in the posting and not as an attachment.

Here are the first 15 records from your file.  I do not see anything that looks like 4.5 or 7.5 in there.

21   options generic;
22   data _null_;
23     infile 'c:\downloads\new.csv';
24     input;
25     if _n_ <15 then put _infile_;
26   run;

NOTE: The infile 'c:\downloads\new.csv' is:
      (system-specific pathname),
      (system-specific file attributes)

WSUB,TIME,DV,CMT,AMT,EVID,wt,SEX
1,0,0,1,30000000,1,23,1
1,0,0,2,30000000,1,23,1
1,0,0,3,0,2,23,1
1,0,0,4,0,2,23,1
1,0,0,5,0,2,23,1
1,0,0,6,0,2,23,1
1,0,0,7,0,2,23,1
1,0,0,8,0,2,23,1
1,0,0,9,0,2,23,1
1,0,0,10,0,2,23,1
1,0,0,11,0,0,23,1
1,0,0,12,0,2,23,1
1,0,0,13,30000000,1,23,1
NOTE: 769 records were read from the infile (system-specific pathname).
      The minimum record length was 10.
      The maximum record length was 32.

[Tom · Accepted solution]

It sounds like you are asking how to do Last Observation Carried Forward.  LOCF.

That can easily be done using the UPDATE statement.

data example;
  infile 'c:\downloads\new.csv' dsd truncover firstobs=2;
  input WSUB TIME DV CMT AMT EVID wt SEX ;
run;

data want;
 update example(obs=0) example;
 by wsub;
 output;
run;

proc compare data=example compare=want;
 id wsub time;
run;

[jacksonan123]

This worked perfectly.  I was not familiar with the update command.  

Thanks