Replace value

[ari]

data have;
input id ntime week time;
datalines;
1 0 1 0
1 0 4 718
1 0 4 718
1 2 4 720
1 2 4 720
1 0 12 .
1 0 12 .
1 2 12 1748
1 2 12 1748
;;

data want;
input id ntime week time;
datalines;
1 0 1 0
1 0 4 718
1 0 4 718
1 2 4 718
1 2 4 720
1 0 12 .
1 0 12 .
1 2 12 .
1 2 12 1748

;;

I want to replace the first value at ntime=2 & week=4 with the value from ntime=0 & week=4

similarly. replace the first value at ntime=2 & week=12 with the value from ntime=0 & week=12

Any ideas to to get want datset?

Thanks,

[Kurt_Bremser]

The value from ntime = 0 & week = 4 is 718.

Why is the first value for ntime = 2 and week = 12 set to missing?

[ari]

@Kurt_Bremser@RW9

There was a mistake in the text. i have edited it. 

[Kurt_Bremser]

Then my code suggestion looks like this:

data have;
input id ntime week time;
datalines;
1 0 1 0
1 0 4 718
1 0 4 718
1 2 4 720
1 2 4 720
1 0 12 .
1 0 12 .
1 2 12 1748
1 2 12 1748
;
run;

data want;
set have;
by id week; /* this is only to guarantee a correct order */
retain keepval flag;
if ntime = 0
then do;
  keepval = time;
  flag = 1;
end;
if ntime = 2 and flag
then do;
  time = keepval;
  flag = 0;
end;
drop keepval flag;
run;

proc print data=want;
run;

Result:

Obs    id    ntime    week    time

 1      1      0        1        0
 2      1      0        4      718
 3      1      0        4      718
 4      1      2        4      718
 5      1      2        4      720
 6      1      0       12        .
 7      1      0       12        .
 8      1      2       12        .
 9      1      2       12     1748

[ari]

@Kurt_Bremser: Perfect, Thanks

[RW9]

Well, odd request, doesn't seem much logic in it?  Anyways the want you gave does not match the logic you give to get it.  This code matches the logic you have given in your text:

data have;
  input id ntime week time;
datalines;
1 0 1 0
1 0 4 718
1 0 4 718
1 2 4 720
1 2 4 720
1 0 12 .
1 0 12 .
1 2 12 1748
1 2 12 1748
;
run;

proc sql;
  create table WANT as 
  select  *,
          (select distinct TIME from HAVE where NTIME=0 and WEEK=4) as REP
  from    HAVE;
quit;

data want;
  set want;
  by id ntime notsorted;
  if first.ntime and ntime=2 then time=rep;
run;

But you will see that 2/12 does not match as "." is not at 0/4, 718 is.

[ari]

@RW9: Thanks but this is not what I wanted. I have updated the text. there was a mistake.

[RW9]

You can change my original code per your update to the logic:

data have;
  input id ntime week time;
datalines;
1 0 1 0
1 0 4 718
1 0 4 718
1 2 4 720
1 2 4 720
1 0 12 .
1 0 12 .
1 2 12 1748
1 2 12 1748
;
run;

proc sql;
  create table WANT as 
  select  *,
          (select distinct TIME from HAVE where NTIME=0 and WEEK=4) as REP1,
          (select distinct TIME from HAVE where NTIME=0 and WEEK=12) as REP2
  from    HAVE;
quit;

data want;
  set want;
  by id ntime notsorted;
  if first.ntime and ntime=2 and week=4 then time=rep1;
  if first.ntime and ntime=2 and week=12 then time=rep2;
run;

[ari]

@RW9: Perfect, thanks