topic Re: replace value based on condition in SAS Programming

replace value based on condition

ari — Wed, 01 Mar 2017 15:01:27 GMT

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

Re: replace value based on condition

Kurt_Bremser — Wed, 01 Mar 2017 14:59:06 GMT

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

Re: replace value based on condition

ari — Wed, 01 Mar 2017 15:04:50 GMT

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

Re: replace value based on condition

jklaverstijn — Wed, 01 Mar 2017 15:57:14 GMT

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.

Re: replace value based on condition

art297 — Wed, 01 Mar 2017 16:49:41 GMT

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

Re: replace value based on condition

Kurt_Bremser — Thu, 02 Mar 2017 08:04:22 GMT

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