topic Re: Assign value from any row by ID in Statistical Procedures

Assign value from any row by ID

bharath86 — Tue, 08 Dec 2020 20:41:53 GMT

@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

Re: Assign value from any row by ID

Reeza — Tue, 08 Dec 2020 19:51:31 GMT

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.

Re: Assign value from any row by ID

bharath86 — Tue, 08 Dec 2020 19:58:32 GMT

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

Re: Assign value from any row by ID

Reeza — Tue, 08 Dec 2020 20:07:14 GMT

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

Re: Assign value from any row by ID

bharath86 — Tue, 08 Dec 2020 20:16:13 GMT

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.

Re: Assign value from any row by ID

bharath86 — Tue, 08 Dec 2020 20:27:23 GMT

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.

Re: Assign value from any row by ID

Reeza — Tue, 08 Dec 2020 20:36:47 GMT

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.

Re: Assign value from any row by ID

bharath86 — Tue, 08 Dec 2020 20:40:12 GMT

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

Re: Assign value from any row by ID

ballardw — Tue, 08 Dec 2020 21:00:03 GMT

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

Re: Assign value from any row by ID

r_behata — Tue, 08 Dec 2020 21:05:29 GMT

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;

Re: Assign value from any row by ID

hhinohar — Tue, 08 Dec 2020 23:44:43 GMT

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;

Re: Assign value from any row by ID

bharath86 — Wed, 09 Dec 2020 03:40:11 GMT

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

Re: Assign value from any row by ID

bharath86 — Wed, 09 Dec 2020 03:55:45 GMT

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

Re: Assign value from any row by ID

hhinohar — Wed, 09 Dec 2020 05:49:16 GMT

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.

Re: Assign value from any row by ID

bharath86 — Wed, 09 Dec 2020 06:23:46 GMT

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.

Re: Assign value from any row by ID

hhinohar — Wed, 09 Dec 2020 06:52:43 GMT

OK. Then dow loop provided might need a modification.

*case of multiple death obs;
data have;
infile datalines dlm="09"x;
input ID $ Transaction Age Death Parameter $;
datalines;
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	11	
8	.	2	10	
9	1	.	 	A	 
9	2	.	 	B	 
9	3	.	 	C	 
9	.	2	11	 	 
9	.	2	12	
9	.	2	13	 	 
9	.	2	11	
; 	 
run;

*1;
data want;
	do until(last.id);
		set have;
		by id;
		v=max(death,v);
	end;
	do until(last.id);
		set have;
		by id;
		if Parameter eq 'B' then
			value=v;
		else call missing(value);
		output;		
	end;
	drop v;
run;

Re: Assign value from any row by ID

bharath86 — Wed, 09 Dec 2020 07:06:14 GMT

Hi Thanks, This works to the extent, but not for multiple if statements. I need to do the same activity for other variables as well. Thanks again.