topic Replace value in SAS Programming

Replace value

ari — Wed, 01 Mar 2017 09:52:49 GMT

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,

Re: Replace value

Kurt_Bremser — Wed, 01 Mar 2017 09:29:12 GMT

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

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

Re: Replace value

RW9 — Wed, 01 Mar 2017 09:41:37 GMT

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.

Re: Replace value

ari — Wed, 01 Mar 2017 09:54:56 GMT

@Kurt_Bremser @RW9

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

Re: Replace value

ari — Wed, 01 Mar 2017 09:58:41 GMT

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

Re: Replace value

RW9 — Wed, 01 Mar 2017 10:01:51 GMT

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;

Re: Replace value

Kurt_Bremser — Wed, 01 Mar 2017 10:07:06 GMT

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

Re: Replace value

ari — Wed, 01 Mar 2017 10:45:08 GMT

@Kurt_Bremser: Perfect, Thanks

Re: Replace value

ari — Wed, 01 Mar 2017 10:46:01 GMT

@RW9: Perfect, thanks