How to make up values based on the other variable while with the same subject

[zimcom]

Hi Greetings!!

I have:

Subject	Visit Id	Score	alive
1005	10	0	Yes
1005	40	2	Yes
1005	50	2	Yes
1005	60	.	Yes
1005	80	.	Yes
1006	10	0	Yes
1006	40	0	Yes
1006	50	2	Yes
1006	60	.	Yes
1006	80	.	Yes
1010	10	0	No
1010	40	.	No
1010	50	.	No
1010	60	.	No
1010	80	.	No
1008	10	0	Yes
1008	40	.	Yes
1008	50	.	Yes
1008	60	.	Yes
1008	80	.	Yes
1009	10	0	Yes
1009	40	4	Yes
1009	50	.	Yes
1009	60	.	Yes
1009	80	.	Yes

I want impute Score of 100 when "alive" is "No" for that subject (differences highlighted):

Subject	Visit Id	Score	alive
1005	10	0	Yes
1005	40	2	Yes
1005	50	2	Yes
1005	60	.	Yes
1005	80	.	Yes
1006	10	0	Yes
1006	40	0	Yes
1006	50	2	Yes
1006	60	.	Yes
1006	80	.	Yes
1010	10	0	No
1010	40	100	No
1010	50	100	No
1010	60	100	No
1010	80	100	No
1008	10	0	Yes
1008	40	.	Yes
1008	50	.	Yes
1008	60	.	Yes
1008	80	.	Yes
1009	10	0	Yes
1009	40	4	Yes
1009	50	.	Yes
1009	60	.	Yes
1009	80	.	Yes

[PaigeMiller]

I am not sure I grasp the full meaning of

I want impute Score of 100 when "alive" is "No" for that subject (differences highlighted):

Based on your example, this seems to be the same as: I want impute Score of 100 when "alive" is "No" 

and no reference to "for that subject" is needed. What am I missing?

[zimcom]

I only want to impute the score "100" for the specific subject though (in above example, it is subject 1010)

[Shmuel · Accepted solution]

Do you mean:

if alive = 'No' and missing(score) then score = 100;

[zimcom]

I only want to impute the score "100" for the specific subject though (in above example, it is subject 1010)

[alpine_nights]

Adding onto Shmuel's code:

If you want it for that specific subject, all you have to add is this

if alive = 'No' and missing(score) and Subject='1010' then score = 100;

If there are multiple subjects you want to do that for, use an in statement with whatever subject you want

if alive = 'No' and missing(score) and Subject in ('1010','1200') then score = 100;

[zimcom]

I have hundreds of subjects and I cannot hard coded the subject like this, I need this to be automated

Subject='1010'

Subject in ('1010','1200') 

[alpine_nights]

What is your criteria for which subjects need to be changed? I don't think we are understanding fully what needs to be done. Maybe you could give another example?

[Shmuel]

If I understand you correctly then you need to add the appropriate condition:

if subject = 1010 and alive = 'No' and missing(score) then score = 100;

[zimcom]

Sorry, probably I wasn't clear, each subject has multiple Visit, and when the alive is "No", I want to impute the Score of "100" to the Score of each Visit of that subject.

I cannot hard code the subject using "subject = "1010", I have hundreds of subject and I want this be automated.

Thank you all for your reply, but still did not solve my problem.

[alpine_nights]

Hmm, I do not believe I understand still.

Could you tell us what is not working with Shmuels's original code:

if alive = 'No' and missing(score) then score = 100;

If you want every score replaced, includes ones that were already there, just remove the missing(score), but the above code does create the output in red you showed. If you describe what this is doing wrong/what else you would like we can help more.

[zimcom]

if alive = 'No' then (for that suject), all the Score (of each Visit) = '100';

[PaigeMiller]

Another question: if a subject has alive='No', can they also have alive='Yes' elsewhere in the data?

Can you modify your original example to make it more relevant to what has been discussed? (Providing a good example helps you get faster answers)

[zimcom]

Another question: if a subject has alive='No', can they also have alive='Yes' elsewhere in the data?
No, if subject died, then alive will be all as "No" for that subject.