Last Observation Carried Foward

[Adambo]

I am trying to perform Last Observation Carried Forward (LOCF) with the dataset “HAVE” shown below. The missing observations which I am trying to carry the last observation to are not included in the dataset so they should be created. I would also like to add a variable (DTYPE) indicating when an observation is carried forward.

HAVE

SUBJID                  VISITN                  AVAL

001                         1                              26

001                         2                              41          

001                         3                              35

002                         1                              12

002                         2                              21

003                         1                              22

WANT

SUBJID                  VISITN                  AVAL                     DTYPE

001                         1                              26

001                         2                              41          

001                         3                              35

002                         1                              12

002                         2                              21

002                         3                              21                           LOCF

003                         1                              22

003                         2                              22                           LOCF

003                         3                              22                           LOCF

[Astounding]

Here's one way:

data want;

set have;

by subjid;

output;

if last.subjid;

dtype='LOCF';

if visitn < 3 then do visitn = visitn + 1 to 3;

   output;

end;

run;

[ballardw]

Is the rule that there should a carry forward if the count of records for the SubjId is less than 3? And that the result should be 3 records total for each Subjid?

[Adambo]

Astounding's solution works perfectly.

Nevertheless, I forgot to mention that the observations I need to carry foward are not alway the last observations. ex

HAVE

SUBJID                  VISITN                  AVAL

001                         1                              26

001                         2                              41          

001                         3                              35

002                         1                              12

002                         3                              21

WANT

SUBJID                  VISITN                  AVAL                     DTYPE

001                         1                              26

001                         2                              41          

001                         3                              35

002                         1                              12

002                         2                              12                          LOCF

002                         3                              21                          

[Ksharp]

data have;
input SUBJID                  VISITN                  AVAL;
cards;
001                         1                              26
001                         2                              41          
001                         3                              35
002                         1                              12
002                         3                              21
;
run;
data want;
 merge have have(keep=subjid visitn rename=(subjid=_subjid visitn=_visitn) firstobs=2);
 output;
 if subjid=_subjid then do;
  do i=visitn+1 to _visitn-1;
   visitn=i;output;
  end;
 end;
 drop _: i;
run;

[Ksharp]

may be this is what you want.


data have;
input SUBJID                  VISITN                  AVAL;
cards;
001                         1                              26
001                         2                              41          
001                         3                              35
002                         1                              12
002                         2                              21
003                         1                              22
;
run;
proc sql;
create table temp as
 select a.*,aval
  from
(select * from (select distinct subjid from have),(select distinct visitn from have))
as a
left join 
have as b
on a.subjid=b.subjid and a.visitn=b.visitn
order by 1,2;
quit;
data want;
 set temp;
 by subjid;
 retain new_aval;
 if first.subjid then new_aval=.;
 if not missing(aval) then new_aval=aval;
 drop aval;
run;

[Adambo]

Ksharp, your first suggestion works well for carrying Middle visit observations foward but not Last visit observations. Perhaps I can combine it with Astounding's solution that carries only Last visit observations foward, and adjust it to fit my specific needs.

Are you able to create a variable (DTYPE) to indicate which observations are carried foward? (if observation is carried foward then DTYPE="LOCF")

I can't get your second suggestion to work. please see the error message below. perhaps it better fits my needs so I would like to see its output.

914       proc sql;
915        create table temp as
916         select a.*,aval
917          from
918           (select * from (select disctinct USUBJID from adhf2),(select distinct AVISITN from
918     !  adhf2))
919           as a
920           left join
921           have as b
922           on a.USUBJID=b.USUBJID and a.AVISITN=b.AVISITN
ERROR: Found "USUBJID" when expecting from
923           order by 1,2;
NOTE: Statements not executed because of errors detected
924           quit;

[Adambo · Accepted solution]

Put together, Astounding's and Ksharp's solution works well to carry forward any observation missing, Here is the code I used:

*Astounding's code; 

data want;

set have;

by SUBJID;

output;

if last.SUBJID;

DTYPE="LOCF";

if VISITN<5 then do VISITN = VISITN+1 to 5;

output;

end;

run;

　

*KSharp's code;

data want2;

merge want want(keep=SUBJID VISITN rename=(SUBJID=_SUBJID VISITN=_VISITN) firstobs=2);

output;

if SUBJID=_SUBJID then do;

do i=VISITN+1 to _VISITN-1;

VISITN=i;

DTYPE="LOCF";

output;

end;

end;

drop _: i;

run;

Thanks guys.

[data_null__]

data aval;
   input SUBJID:$3. VISITN AVAL;
   cards;
001 1 26
001 2 41
001 3 35
002 1 12
002 2 21
003 1 22
;;;;
   run;
proc print;
   run;
proc summary data=aval nway;
   class visitn;
   output out=classdata(drop=_:);
   run;
proc summary data=aval classdata=classdata nway;
   by subjid;
   class visitn;
   output out=framed(drop=_type_) idgroup(out(aval)=);
   run;
data locf;
   update framed(obs=0 keep=subjid) framed;
   by subjid;
   if _freq_ eq 0 then dtype = 'LOCF';
   drop _freq_;
   output;
   run;
proc print;
   run;

[Ksharp]

Table ' adhf2' and 'have' should has the same name . you used two different tables, that is not right.

[Adambo]

That is an oversight on my part. I changed it and I still get the following error message:

1101      proc sql;
1102       create table temp as
1103        select a.*,aval
1104         from
1105          (select * from (select disctinct USUBJID from adhf2),(select distinct AVISITN from
1105    !  adhf2))
1106          as a
1107          left join
1108          adhf2 as b
1109          on a.USUBJID=b.USUBJID and a.AVISITN=b.AVISITN
ERROR: Found "USUBJID" when expecting from
1110          order by 1,2;
NOTE: Statements not executed because of errors detected
1111          quit;

[Ksharp]

Can you post some data from adhf2, and I would like to debug the code.

[Adambo]

the data from adhf2 is a little sensible (it contains patient's data) and it would require me some time to de-identify it and convert it in a form that I can share with the community. As I mentioned above, your first solution works just fine. Thanks for that.