topic Re: Last Observation Carried Foward in SAS Programming

Last Observation Carried Foward

Adambo — Fri, 04 Nov 2016 16:04:00 GMT

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

Re: Last Observation Carried Foward

Astounding — Fri, 04 Nov 2016 16:52:51 GMT

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;

Re: Last Observation Carried Foward

ballardw — Fri, 04 Nov 2016 16:58:06 GMT

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?

Re: Last Observation Carried Foward

Adambo — Fri, 04 Nov 2016 18:09:45 GMT

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

Re: Last Observation Carried Foward

Ksharp — Sat, 05 Nov 2016 10:15:29 GMT


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;

Re: Last Observation Carried Foward

Ksharp — Sat, 05 Nov 2016 10:24:28 GMT

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;

Re: Last Observation Carried Foward

Adambo — Mon, 07 Nov 2016 15:52:47 GMT

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;

Re: Last Observation Carried Foward

Adambo — Mon, 07 Nov 2016 16:12:41 GMT

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;

drop _: i;

run;

Thanks guys.

Re: Last Observation Carried Foward

Ksharp — Tue, 08 Nov 2016 02:47:10 GMT

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

Re: Last Observation Carried Foward

Adambo — Tue, 08 Nov 2016 14:26:49 GMT

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;

Re: Last Observation Carried Foward

data_null__ — Tue, 08 Nov 2016 15:09:53 GMT

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;

Re: Last Observation Carried Foward

Ksharp — Wed, 09 Nov 2016 04:23:59 GMT

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

Re: Last Observation Carried Foward

Adambo — Wed, 09 Nov 2016 15:08:52 GMT

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.