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
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.
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;
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?
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
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;
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;
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;
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 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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.