Assign value from any row by ID

[bharath86]

@Kurt_Bremser @PeterClemmensen @hhinohar 

Hi, 

Below is an example of data I have. 

My requirement is, If my parameter is B then value = Death, but in this case the row is blank however there is a death value in some other row. 

It doesnt matter in which row the value is in but the code should look in all rows for an ID and assign the value. 

I want this to be in a data step as I have numerous if statements to process and there may be multiple ID's but the transaction rows are always empty, code should look at all rows beyond transaction 3 to find the value. 

Data I have

ID	Transaction	Age	Death	Parameter	Value
6	1	.	.	A
6	2	.	.	B
6	3	.	.	C
6	.	3	3.45

Data I want

ID	Transaction	Age	Death	Parameter	Value
6	1	.	.	A
6	2	.	.	B	3.45
6	3	.	.	C
6	.	3	3.45

Please advise.

Thank you

[Reeza]

Do you have multiple IDs? Will all Value be missing or could it be filled in for some?
It would be helpful to expand your example data to include a few more scenarios.

[bharath86]

Hi

I have multiple Id's and transaction 1,2,3 are always blank. I only need to look for values in other than (1,2,3) rows. I may have any number of rows after transaction 3 but i need to look into all the rows and assign the value. 

ID	Transaction	Age	Death	Parameter	Value
6	1	.	.	A
6	2	.	.	B
6	3	.	.	C
6	.	3	3.45
8	1	.		A
8	2	.		B
8	3	.		C
8	.	2	12
8	.	2	12

[Reeza]

SQL seems a good method here. 

If you take the maximum of death column and only assign it to B that should work.

proc sql;
create table want as
select *, case when parameter = "B" then max(Death) end as ValueWanted
from have
group by ID
order by ID, transaction;
quit;

Untested - if this doesn't work, post the code and log and a description of what isn't working.

---

@bharath86 wrote:

Hi

I have multiple Id's and transaction 1,2,3 are always blank. I only need to look for values in other than (1,2,3) rows. I may have any number of rows after transaction 3 but i need to look into all the rows and assign the value. 

ID	Transaction	Age	Death	Parameter	Value
6	1	.	.	A
6	2	.	.	B
6	3	.	.	C
6	.	3	3.45
8	1	.		A
8	2	.		B
8	3	.		C
8	.	2	12
8	.	2	12

---

[bharath86]

SQL may be working but I need it in a data step as I have numerous if statements to be processed under similar scenario. For me the Ideal way would be data step. Thank you.

[bharath86]

I have tested your code it works but this doesnt serve my purpose using sql, It has to be in a data step in order to process all the other statements.

[Reeza]

This will need multiple data steps or a DoW loop. A DoW loop is definitely more work to code though and it may change you structure your IF statements. I'd highly recommend splitting it up but ultimately your choice on how to do it. Hopefully someone else can provide a solution within your requested parameters. It would be helpful if you stated such limitations in your original question.

[bharath86]

Hey sorry about that, I didnt knew. Anyways thanks for checking in.

[ballardw]

---

@bharath86 wrote:
I have tested your code it works but this doesnt serve my purpose using sql, It has to be in a data step in order to process all the other statements.

---

Take the output from the SQL example and use that as input to a data step.

Insisting on solutions that use a single data set may be much more complicated then getting the pieces you need from another procedure and then additional processing.

[r_behata]

data have;
input ID $ Transaction Age Death Parameter $;
infile cards4 truncover;
cards;
6 1 . . A
6 2 . . B
6 3 . . C
6 . 3 3.45
;
run;


data want;
	do _n_=1 by 1 until(last.id | death ne .);
		set have;
		by id;
		v=death;
	end;

	do _n_=1 to _n_;
		set have;

		if Parameter eq 'B' then
			value=v;
		else call missing(value);
		output;
	end;

	drop v;
run;

[hhinohar]

This seems like a look up query.I would use a hash in this case.

data have;
input ID $ Transaction Age Death Parameter $;
infile cards4 truncover;
cards;
6 1 . . A
6 2 . . B
6 3 . . C
6 . 3 3.45
;
run;

data want;
length ID $ 8 Transaction Age Death 8 Parameter $8;
  if _N_=1 then do;
    *keep id and death variable where variable stores data;
    if 0 then set have(drop=transaction age rename=(Death=value) where=(value));
    dcl hash h(dataset:"have(drop=transaction age rename=(Death=value) where=(value))");
    h.definekey("id");
    h.definedata("value");
    h.definedone();
  end;
  set have;
  *look up have dataset so that all related transaction be filled;
  _iorc_=h.find();
  if parameter ne "B" then value=.;
run;

[bharath86]

Value retains values of first ID to the next ID instead of looking for new values for next ID

[bharath86]

ID	Transaction	Age	Death	Parameter	Value
6	1	.	.	A
6	2	.	.	B
6	3	.	.	C
6	.	3	3.45
8	1	.		A
8	2	.		B
8	3	.		C
8	.	2	12
8	.	2	12

[hhinohar]

Hi @bharath86 

It works fine here.

If values keeps missing for entire observation then length statement might be doing something.

data want;
  if _N_=1 then do;
    if 0 then set have(drop=transaction age rename=(Death=value) where=(value));
    dcl hash h(dataset:"have(drop=transaction age rename=(Death=value) where=(value))");
    h.definekey("id");
    h.definedata("value");
    h.definedone();
  end;
  set have;
  _iorc_=h.find();
  if parameter ne "B" then value=.;
run;

If this is not the case, please send log.

[bharath86]

Hi Yeah this is working, reason for the failure is in some cases the values are not the same like in ID, for instance ID has 2 rows with value 12 but in some cases there are different values, in such cases is should take the max value. I am thinking how to assign max value for death.