DATA Step, Macro, Functions and more

replace value based on condition

Reply
Frequent Contributor
Frequent Contributor
Posts: 103

replace value based on condition

[ Edited ]

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

Super User
Posts: 7,805

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

Re: replace value based on condition

Posted in reply to KurtBremser

@KurtBremser: 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
Posts: 7,805

Re: replace value based on condition


ari wrote:

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 441

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.

PROC Star
Posts: 7,474

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

 

Ask a Question
Discussion stats
  • 5 replies
  • 212 views
  • 0 likes
  • 4 in conversation