Get name of specific variable which name is equal to another value of a variable

[Marie-Louise Guldfeldt]

I have a study where I am to look 28 days back in time before an event to see if the patients were working or not working.
Each patient has a different event date (i.e. var date_ascd).

Relevant variables:
y_yyww (year and week, e.g. y_1410 means year 2014 and week 10. Format %10.0g and type int)
date_ascd (date format %tdD_m_Y, type long)
status_onemonthprior (date format %td, type float)
bbcombination (str6, %9s)

The variable y_yyww tells me if the patient was working or not prior to the event using different values (e.g. value 299 means the patient received sick leave pay). If the patient has a variable with the name y_1410 with the value of 299 it means the patient received sick leave pay in 2014, week 10. This variable is reported weekly. The variable y_yyww reaches a maximum of 52 weeks.

The var status_onemonthprior tells me the date 4 weeks prior to the event date.

The var bbcombination is a var calculated using the date from status_onemonthprior and we end up with string values of e.g. y_1410 if we want to look through the y_yyww variable using that name.


My issue
I would like go through all of my y_1401-y_1501 variables and see if any of the variables has the same name as bbcombination's value. If that is the case then I would like to generate a new variable called y_onemonthprior that has the same value as the value that has the same name as bbcombination's value.

Meaning, I would end up with a new variable called y_onemonthprior with the value of e.g. 299. This value can differ from patient to patient according to work status.


Is this in any way possible?

[Clyde Schechter]

Yes, but it is complex enough that you should use the -dataex- command to show example data. Descriptions of data are well-meant but insufficient to help those who want to help you. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Marie-Louise Guldfeldt]

Thank you for your reply. Since I am accessing my data through a secured remote connection, I can't take out specific data - I have therefore created mock-up data that mimics my own data, see example below.
Missing data in variable y_1411 means a person have not received any social benefits and we assume this person is working at that time.

* Example generated by -dataex-. For more info, type help dataex
clear
input long date_ascd float(status_onemonthprior day month shortyear) str4 bcombination str6 bbcombination byte week str2 bweek int(y_1403 y_1409 y_1410 y_1411)
19764 19927 23 7 14 "1430" "y_1430" 30 "30" 730 730 730   .
19811 19783  1 3 14 "1409" "y_1409"  9 "09" 783 783 783 999
end
format %tdD_m_Y date_ascd
format %td status_onemonthprior

[Clyde Schechter]

The most direct way to do this is:

gen y_onemonthprior = .

foreach v of varlist y_* {
    replace y_onemonthprior = `v' if bbcombination == "`v'"
}

I don't know where you are going with this. I doubt that I would have set the data up this way myself, and I suggest you switching over to a long data layout, and using actual Stata internal weekly dates instead of codes like 1430 and 1409. Most data management and analysis are simpler that way.

Note that in your example this produces a missing value for the first observation because bbcombination == "y_1430" and, at least in your example, there is no variable y_1430. (Typo?)

Added: One other thing puzzles me about your data example. In #1 you said that status_onemonthprior was a date four weeks earlier than date_ascd. But in the first observation, status_onemonthprior (and also the variables day, montbh, shortyear, bcombination and bbcombination) are actually more than 5 months after date_ascd.

[Marie-Louise Guldfeldt]

Thank you very much for your reply and your advise about the long data layout and internal weekly dates.
Your code works perfectly and will save me a lot of time.

Sorry for the inconveniences with the incorrect first observation of status_onemonthprior (and the following var), you are correct, it was a mistake made when I first made the mock-up data.