topic Re: How to populate LOCF values to all missing obs in SAS Programming

How to populate LOCF values to all missing obs

knveraraju91 — Thu, 17 Aug 2017 22:55:16 GMT

Dear,

In my data three subjects with 11 obs present. The subject 'a' has all visit obs. The subject 'b' and 'c' has a few missing visits. I need to populate all the visits with values from previous 'aval' variable values. Each subject should have all the visit obs

Eg;

Subject b has missing visit 2. The 'visitnum= 2' obs is populated with aval from visitnum;=1

Thank you

data one;
input name $ visitnum aval;
datalines;
a 1 10
a 2 20
a 3 30
a 4 40
a 5 50
b 1 10
b 3 30
b 4 40
b 5 50
c 1 10
c 2 20
;

out put needed;

a 1 10
a 2 20
a 3 30
a 4 40
a 5 50
b 1 10
b 2 10
b 3 30
b 4 40
b 5 50
c 1 10
c 2 20
c 3 20
c 4 20
c 5 20

Re: How to populate LOCF values to all missing obs

novinosrin — Thu, 17 Aug 2017 23:11:49 GMT

data one;

input name $ visitnum aval;

datalines;

a 1 10

a 2 20

a 3 30

a 4 40

a 5 50

b 1 10

b 3 30

b 4 40

b 5 50

c 1 10

c 2 20

;

data want;

set one;

by name;

_k=lag(visitnum);

_k1=lag(aval);

_visitnum=visitnum;

_aval=aval;

if not first.name and dif(visitnum)>1 then do;

visitnum=_k+1;

aval=_k1;

output;

visitnum=_visitnum;

aval=_aval;

output;

end;

else output;

drop _:;

run;

Re: How to populate LOCF values to all missing obs

art297 — Fri, 18 Aug 2017 00:12:37 GMT

The following should work:

data one;
  input name $ visitnum aval;
  datalines;
a 1 10
a 2 20
a 3 30
a 4 40
a 5 50
b 1 10
b 3 30
b 4 40
b 5 50
c 1 10
c 2 20
;
 
data want (keep=name visitnum aval);
  set one;
  by name;
  array avals(5);
  retain avals:;
  if first.name then call missing(of avals(*));
  avals(visitnum)=aval;
  if last.name then do i=1 to 5;
    if missing(avals(i)) then avals(i)=avals(i-1);
    aval=avals(i);
    visitnum=i;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

Re: How to populate LOCF values to all missing obs

Ksharp — Fri, 18 Aug 2017 13:33:38 GMT


data one;
  input name $ visitnum aval;
  datalines;
a 1 10
a 2 20
a 3 30
a 4 40
a 5 50
b 1 10
b 3 30
b 4 40
b 5 50
c 1 10
c 2 20
;

proc sql;
create table temp as
 select a.*,b.aval
  from (
 select * from 
 (select distinct name from one),
 (select distinct visitnum from one)
 ) as a left join one as b
 on a.name=b.name and a.visitnum=b.visitnum ;
quit;


data want;
 set temp;
 by name;
 retain new;
 if first.name then call missing(new);
 if not missing(aval) then new=aval;
run;