topic Re: LOCF in a dataset in SAS Procedures

LOCF in a dataset

michtka — Mon, 15 Apr 2013 16:22:16 GMT

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.

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;

Re: LOCF in a dataset

Haikuo — Mon, 15 Apr 2013 16:48:06 GMT

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

Re: LOCF in a dataset

michtka — Mon, 15 Apr 2013 16:54:00 GMT

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.

Re: LOCF in a dataset

DBailey — Mon, 15 Apr 2013 17:22:36 GMT

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;

Re: LOCF in a dataset

michtka — Mon, 15 Apr 2013 17:37:23 GMT

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,

Re: LOCF in a dataset

Haikuo — Mon, 15 Apr 2013 17:41:37 GMT

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

Re: LOCF in a dataset

michtka — Mon, 15 Apr 2013 17:47:56 GMT

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.