replace value based on condition

[ari]

data have;
input is time week value1 value2
datalines;
1 0 1 1 2
1 0 4 1 2
1 0 4 1 2
1 1000 8 1 2
1 1000 8 1 2
1 0 24 1 2
1 0 24 1 2
1 2 24 1 2
1 2 24 1 2
1 0 48 1 2
1 0 48 1 2
1 2 48 1 2
1 2 48 1 2
;;

data want;
input ID time week value1 value2;
datalines;
1 0 1 1 2
1 0 4 0 0
1 0 4 1 2
1 1000 8 3 4
1 1000 8 1 2
1 0 24 5 6
1 0 24 1 2
1 2 24 7 8
1 2 24 1 2
1 0 48 4 5
1 0 48 1 2
1 2 48 6 5
1 2 48 1 2

;;

if there are two rows with same time and week, I am trying to replace the value1 and value 2 with different values.

Is there a way to achieve this output?

Thanks

[Kurt_Bremser]

data want;
set have;
by time notsorted week notsorted;
if not first.week
then do;
  value1 = 0;
  value2 = 0;
end;
run;

[ari]

@Kurt_Bremser: Thanks, what if the values need to be replaced with values other than 0. I have updated the text to represent such a case. Is there a way to identify and specifically modify the first row (of the two rows with same time and week values)

[Kurt_Bremser]

---

@ari wrote:

@Kurt_Bremser: Thanks, what if the values need to be replaced with values other than 0. I have updated the text to represent such a case. Is there a way to identify and specifically modify the first row (of the two rows with same time and week values)

---

Then we need to know from where to retrieve those replacement values.

If you need to replace only the first line of a multiple of identical lines, I suggest

- run a data step where you set a counter variable from _n_

- sort the dataset by descending counter (you reverse the order completely)

- use by as already suggetse in the next data step, and set your values only when last. and not first. of the last by variable is met

- recreate the original order by sorting by counter; if you use out= in the proc sort to create a new dataset, you can drop counter there.

[jklaverstijn]

In no way trying to solve your problem, more a remark of a general nature: your tables seem to be missing a identifying key. Even ID+time+week is not unique in your data. The lack of a so-called primary key is generally speaking a problem when solving this class of lookup puzzles. If there was such a key the solution would be perfectly simple.

Regards,

- Jan.

[art297]

Not sure if the following meets your conditions, but should come close enough that you can adjust it:

data want (drop=i);
  set have;
  by time notsorted week notsorted;
  array used1(999) _temporary_;
  array used2(999) _temporary_;
  retain used1 used2;
  if first.week then do;
    i=0;
    call missing(of used1(*));
    call missing(of used2(*));
  end;
  i+1;
  if value1 not in used1 then used1(i)=value1;
  else do;
    value1=max(of used1(*))+ceil(100*rand("Uniform"));
    used1(i)=value1;
  end;
  if value2 not in used2 then used2(i)=value2;
  else do;
    value2=max(of used2(*))+ceil(100*rand("Uniform"));
    used2(i)=value2;
  end;
run;

Art, CEO, AnalystFinder.com