topic Move values of a variable where values of another variable appear in New SAS User

Move values of a variable where values of another variable appear

NewUsrStat — Thu, 10 Oct 2024 16:52:59 GMT

Hi guys,

suppose to have the following:

data DB;
  input ID :$20. Event Index;
cards;
0001 1  0
0001 0  1
0002 1  1
0002 0  0
0003 1  0
0003 0  2
0003 0  0
0003 0  0
0003 0  0
run;

Is there a way to get the following?

data DB1;
  input ID :$20. Event Index;
cards;
0001 1  1
0001 0  0
0002 1  1
0002 0  0
0003 1  2
0003 0  0
0003 0  0
0003 0  0
0003 0  0
run;

In other words the value of Index should be moved where event = 1 when it is already not there. Only moved. No other calculation is required.

Thank you in advance

Re: Move values of a variable where values of another variable appear

antonbcristina — Thu, 10 Oct 2024 17:27:09 GMT

Hi @NewUsrStat, here's some code that will get the job done but I should mention it's convoluted and makes a few assumptions which may or may not work in all situations.

data db_temp;
	retain max_index;
	set db;
	by id;
	
	if first.id then max_index=0;
	max_index=max(max_index,index);
	if last.id;
	keep id max_index;
run;

data db1;
	merge db db_temp;
	by id;
	if event=1 then index=max_index;
	else if index=max_index then index=0;
run;

Happy to iterate if you can provide more information about whether each ID will have at least one observation where event=1, whether max(index) is what we're looking for, ...

Re: Move values of a variable where values of another variable appear

Tom — Thu, 10 Oct 2024 18:23:32 GMT

Your need to explain more about the meaning of these variables. And why you want to "move" the values. How did they get in the wrong place to begin with? Can you instead fix the process that put them in the wrong place?

Does each ID only have one observation where EVENT=1?

Is it always the first observation like it is in your examples?

If so then I would recommend just merging the data with itself.

data DB;
  input ID :$20. Event Index;
cards;
0001 1  0
0001 0  1
0002 1  1
0002 0  0
0003 1  0
0003 0  2
0003 0  0
0003 0  0
0003 0  0
;

data want;
  merge db(drop=index) db(keep=id index where=(index));
  by id;
  if not first.id then index=0;
run;

Re: Move values of a variable where values of another variable appear

NewUsrStat — Thu, 10 Oct 2024 18:36:23 GMT

Hi Tom. The values (of "Index" variable) are not in the desired order because two tables were merged and the one containing the values not in the desired order was used for other calculation for which that order had to be maintained.
Does each ID only have one observation where EVENT=1? yes absolutely.

Is it always the first observation like it is in your examples? Yes it is absolutely.

Re: Move values of a variable where values of another variable appear

Ksharp — Fri, 11 Oct 2024 01:19:28 GMT

data DB;
  input ID :$20. Event Index;
cards;
0001 1  0
0001 0  1
0002 1  1
0002 0  0
0003 1  0
0003 0  2
0003 0  0
0003 0  0
0003 0  0
;

proc sql;
create table want as
select ID,Event,case when Event then max(Index) else 0 end as Index
 from DB 
  group by ID;
quit;