BookmarkSubscribeRSS Feed
ari
Quartz | Level 8 ari
Quartz | Level 8

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
ari
Quartz | Level 8 ari
Quartz | Level 8

@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
Super User

@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
Rhodochrosite | Level 12

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
Opal | Level 21

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

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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