What is the best way to look for a string containing certain characters and make a new variable?

[raajdesaii]

Hi everyone!!

I needed some help to find multiple variables "YEAR10_1,YEAR10_2.. so on" containing a diagnostic code string to make a new variable "Year_Diagnosis". The data looks as follows:

ID	YEAR10_1	YEAR10_2	YEAR10_3	YEAR11_1	YEAR11_2	YEAR12_1	YEAR12_2	YEAR12_3	YEAR_DIAGNOSIS
1				C34.0	C50.2	C34.0	C34.2		2011
2	C34.0	C50.2	C34.0	C34.2					2010
3						C34.0	C50.2	C34.0	2012
4	C34.0								2010
5	C50.2	C34.0		C34.0					2011
6				C34.0					2011

I am interested in the string starting with "C34" and the first one that appears in the column. For example, in ID=1, "C34" appears in YEAR11_1 and YEAR12_1 but I want the output diagnosis year as 2011. 

I am sure there are many ways to do this but I was hoping if someone could help me find a more efficient way since I have do this many times with my data and on thousands of patients.

Thanks!!!!

[ChrisNZ]

Your data structure certainly ensures that performing any calculation will be a pain.

Structure your data vertically:

ID YEAR N DIAG

1  2011 1 C34.0

[raajdesaii]

There are more diagnosis years for the following years until 2020, this is how the data has been structured. I was able to get the results using if/then/else but I am sure there is a better and efficient way of doing this. Any help will be much appreciated.

[ChrisNZ]

When you hit 2022, your program won't work anymore.

Just don't do this. The structure shown looks like a report.

Data should never be stored in this manner.

Change your table.

Hint: Column names should not contain data.