BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Adambo
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Adambo
Obsidian | Level 7

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.

View solution in original post

12 REPLIES 12
Astounding
PROC Star

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
Super User

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
Obsidian | Level 7

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
Super User

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
Super User
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
Obsidian | Level 7

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
Obsidian | Level 7

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__
Jade | Level 19
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;

Capture.PNG

 

Ksharp
Super User
Table ' adhf2' and 'have' should has the same name . you used two different tables, that is not right.
Adambo
Obsidian | Level 7

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
Super User
Can you post some data from adhf2, and I would like to debug the code.
Adambo
Obsidian | Level 7
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 9220 views
  • 2 likes
  • 5 in conversation