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
data want;
set have;
by time notsorted week notsorted;
if not first.week
then do;
value1 = 0;
value2 = 0;
end;
run;
@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)
@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.
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.