Apply a Conditional Value for a New Variable

[anissak1]

Hello.  I have matched pairs of observations in a long form.  I want to create a new variable "Cohort Censor".

For each case (case=1), Cohort Censor should equal walkcensordate.  For each control (case=0), it should be the value of the case with that has same match number.  Short dataset attached.

This seems easy, but I'm still a relative neophyte and scratching my head how I can reference an indicator variable in a conditional way to create the new variable.

Thank you for any guidance

Anissa

[anissak1]

I would like to clarify. Each control (case=0) should have a cohortcensor date that is the walkcensordate for its matched case (case=1).

[PGStats · Accepted solution]

One way to do this is with SQL:

proc sql;
create table forsas6 as
select *,
	(select walkCensorDate from forsas5 as b where b.matchNumber=a.matchNumber and b.case = 1) as cohortcensorDate 
from forsas5 as a;
quit;

[anissak1]

Thank you for this code! It worked perfectly. I don’t knOw SQL, but learned a bit from this example. :). Much appreciated!

[sustagens]

proc sql;
create table want as 
select t1.*,
(case when t1.case=1 then t1.walkcensordate else t3.walkcensordate end) as cohortcensor
from forsas5 t1 
left join (select t2.* from forsas5 t2 where t2.case=1) t3 
on t1.matchnumber=t3.matchnumber;
quit;