String variable containing variable names

[woodmaster]

Hey guys...

My name is Felix, and I'm new here to this forum...
Actually, statalist.org has helped me a lot within the course of the last year, but now I face a problem I can't find any answers for.
So any kind of help would be highly appreciated.

I have a panel dataset on hand, basically in the form of the table shown below...
(The table only shows 1 ID. Naturaly, it should apply to all IDs in the end.)

The table shows the initial situation (first 6 columns) and the intended solution (last 2 columns).

So what I want to do is rather simple...(maybe even the answer to the problem is obvious and it's just me who is really puzzled...)

I want to multiply the value in column "Quan" with one of the values from the columns "FR01" or "AT07", just depending on whether column "BS" contains the respective variable name as string, and want to put the result in a new variable, again distinguishing between the variables, respectively.

So the main question is, how can I "read" the content of the string variable "BS" and tell Stata that this is actually the name of an existing variable?
(Maybe it's important to note that this is only a very condensed version...my datafile will contain about 700 different variables like "FR01" or "AT07", referring to different stocks)

Looking forward to your support!
Thanks in advance,

Best wishes,
Felix

ID	Date	BS	Quan	FR01	AT07	V(FR01)	V(AT07)
1	07/08/2005	FR01	15.0	22.05	7.25	330.75	.
1	07/09/2005	.	.	22.50	7.15	.	.
1	07/10/2005	AT07	15.0	23.50	7.00	.	105.00
1	07/11/2005	FR01	-7.0	22.85	7.55	-159.95
1	07/12/2005	.	.	24.05	7.90	.	.
1	07/13/2005	AT07	-10.0	25.00	8.10	.	-81.0

[Roberto Ferrer]

Will this work for your real data?

clear all
set more off

*----- example data -----

input ///
ID     str20 Date     str5 BS     Quan     FR01     AT07     VFR01      VAT07
1     "07/08/2005"     "FR01"        15.0     22.05     7.25     330.75        .
1     "07/09/2005"      ""           .         22.50     7.15     .            .
1     "07/10/2005"     "AT07"        15.0     23.50     7.00     .          105.00
1     "07/11/2005"     "FR01"        -7.0     22.85     7.55     -159.95    .
1     "07/12/2005"      ""        24.05     7.90     .         .          .
1     "07/13/2005"     "AT07"        -10.0     25.00     8.10     .           -81.0
end

drop Date V*

list

*----- what you want -----

gen VFR = Quan * FR01 if BS == "FR01"
gen VAT = Quan * AT07 if BS == "AT07"

list

If not, please explain why, using example data. Please follow advice in the footer.

[Clyde Schechter]

The fact that you will have hundreds of variables mentioned in the BS variable makes it a bit more complicated than it would be if you had only 2, but it isn't all that hard.

levelsof BS, local(variables)
foreach v of local variables {
    confirm var `v'
    gen V_`v' = Quan*`v' if BS == "`v'"
}

Note that I have named your variables V_*, not V(*), because you cannot have parentheses in a legal Stata variable name.

Also note that the entire scheme is only possible if all of the values listed in BS are actually names of variables in your data set. The -confirm- statement serves to check that for you.

So it's easy enough to do. It's a little hard for me to guess why you want to do this: creating a sparse wide layout seems less useful than the more compact form in which you already have this data. Almost anything you want to calculate in Stata would be more easily done with the data in the layout you already have. But that's up to you.

[woodmaster]

[...]

levelsof BS, local(variables)
foreach v of local variables {
confirm var `v'
gen V_`v' = Quan*`v' if BS == "`v'"
}

[...]
So it's easy enough to do. It's a little hard for me to guess why you want to do this: creating a sparse wide layout seems less useful than the more compact form in which you already have this data. Almost anything you want to calculate in Stata would be more easily done with the data in the layout you already have. But that's up to you.

Thank you so much for your quick answer! It works just perfectly fine!
Even though it may seem rather excresent, I guess this kind of tedious solution is the only way to cope with the problem.
Actually, FR01 and AT07 refer to certain stocks whereas the strings in "BS" indicate which stock was bought/sold at a given date...
In order to "reconstruct" the respective portfolios, I have to go the long way to be able to compound returns as portfolio-compositions change incessantly.
Of course the problem stated in the previous post is only a snippet of the entire problem...but, actually, a very essential one.