Quartz | Level 8

replace value based on condition

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

5 REPLIES 5
Super User

Re: replace value based on condition

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

Re: replace value based on condition

@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)

Super User

Re: replace value based on condition

@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.

Rhodochrosite | Level 12

Re: replace value based on condition

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.

Opal | Level 21

Re: replace value based on condition

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

Discussion stats
• 5 replies
• 7604 views
• 0 likes
• 4 in conversation