DATA Step, Macro, Functions and more

Replace value

Reply
Frequent Contributor
Frequent Contributor
Posts: 101

Replace value

[ Edited ]

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,

Super User
Posts: 6,946

Re: Replace value

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Frequent Contributor
Posts: 101

Re: Replace value

@KurtBremser@RW9

 

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

Super User
Posts: 6,946

Re: Replace value

[ Edited ]

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Frequent Contributor
Posts: 101

Re: Replace value

@KurtBremser: Perfect, Thanks

Super User
Super User
Posts: 7,407

Re: Replace value

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.

Frequent Contributor
Frequent Contributor
Posts: 101

Re: Replace value

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

Super User
Super User
Posts: 7,407

Re: Replace value

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;
Frequent Contributor
Frequent Contributor
Posts: 101

Re: Replace value

@RW9: Perfect, thanks

Ask a Question
Discussion stats
  • 8 replies
  • 179 views
  • 0 likes
  • 3 in conversation