LOCF in a dataset

[michtka]

Hi everyone, I've got the next dataset (have), and I would like to generate a dataset (want) populating the RESPONSE

variable with the LOCF (capital in my final dataset (want)), rather than no change:

Any help?

Thnaks in advance.

V.

data have;

length subjid visit 8. sys response $10;

input subjid visit $ sys $ response;

datalines;

1 1 body normal     

1 2 body abnormal  

1 3 body no change

1 1 heart normal

1 2 heart no change

1 3 heart no change

2 1 body  normal

2 2 body  abnormal

2 3 body  no change

2 4 body  no change

2 5 body  no change

2 6 body  no change

2 7 body  normal

2 1 heart not done

2 2 heart normal

2 3 heart no change

;

run;

This is the dataset WANT I would like to create (notice now than the no change values has been changed by the LOCF records)  ;

1 1 body normal     

1 2 body abnormal  

1 3 body ABNORMAL

1 1 heart normal

1 2 heart NORMAL

1 3 heart NORMAL

2 1 body  normal

2 2 body  abnormal

2 3 body  ABNORMAL

2 4 body  ABNORMAL

2 5 body  ABNORMAL

2 6 body  ABNORMAL

2 7 body  normal

2 1 heart not done

2 2 heart normal

2 3 heart NORMAL

;

run;

[Haikuo · Accepted solution]

data have;

input subjid visit $ sys $ response :&$20.;

datalines;

1 1 body normal 

1 2 body abnormal 

1 3 body no change

1 1 heart normal

1 2 heart no change

1 3 heart no change

2 1 body normal

2 2 body abnormal

2 3 body no change

2 4 body no change

2 5 body no change

2 6 body no change

2 7 body normal

2 1 heart not done

2 2 heart normal

2 3 heart no change

;

data want;

  set have;

  length _t $20.;

  retain _t;

  response=ifc(response='no change', _t, response);

_t=upcase(response);

drop _t;

run;

Haikuo

[michtka]

Dear Hai.kuo,

pleas,e could you supply ifc for another SAS code, because this function is not working in SAS 9.0.

Many thanks.

[Haikuo]

Usually ifn() or ifc() can be replace by 'if then; else;', In your case, it turned out IFC() is not making code more precise, as the 'else' part is not needed.

data want;

  set have;

  length _t $20.;

  retain _t;

/* response=ifc(response='no change', _t, response);*/

  if response='no change' then response=_t;

_t=upcase(response);

drop _t;

run;

Haikuo

[michtka]

Brilliant, I just worked out how the ifn or ifc works, and I arrived to the same conclusion than yours, the else part is not needed.

Thank you very much.

V.

[DBailey]

Just confirming...you have two datasets (have and LOCF).  Both have a column called subjid which relates records...

proc sql;

create table want as

select

     t1.subjid,

     t1.visit,

     t1.sys,

     coalesce(t2.response, t1.response) as response

from

     have t1

     left outer join locf t2

          on t1.subjid=t2.subjid

;

quit;

[michtka]

DBaile, I only have 1 dataset, the dataset HAVE, the other dataset need to be created using LOCF instead of "no change".

Hai.kuo gave the solution using the function retain and ifc.

Thanks,

V.