Assigning values from Variables to observations

[Andi Smith]

Hello all,

i have a problem with assigning values from daily observations to a new variable which contains only one specific value out of the daily observations.
The logic behind my problem is the same as the Index, Match, Match function in Excel solves, but i don't know how to do it with Stata.
Attached you can see my dataexample.

* Example generated by -dataex-. For more info, type help dataex
clear
input float MV_A str12 A_ISIN int(DateAnnounced Date) long JP3200450009 double(AU000000FGL6 AU000000TCL6)
. "AU000000FGL6" 16453 16439 1178434 11635.21 3602.64
. "DK0010207497" 16463 16440 1148001 11655.13  3640.4
. "AU000000TCL6" 16467 16441 1141238 11555.52 3667.97
. "JP3200450009" 16467 16442 1131939 11635.21 3721.91
. ""                 . 16443 1131939 11655.13 3754.28
. ""                 . 16446 1159835 11416.05 3705.73
. ""                 . 16447 1135320 11256.67 3651.79
. ""                 . 16448 1137011 11256.67 3624.82
. ""                 . 16449 1151382 11256.67    3641
. ""                 . 16450 1151382 10499.58 3667.97
. ""                 . 16453 1175898 10698.81 3667.97
. ""                 . 16454 1158145 10559.46 3630.22
. ""                 . 16455 1155609 10599.32    3641
. ""                 . 16456 1154763 10479.77 3635.61
. ""                 . 16457 1155609 10459.85 3603.25
. ""                 . 16460 1151382 10360.23 3587.06
. ""                 . 16461 1167444 10360.23 3587.06
. ""                 . 16462 1170825 10240.69 3652.66
. ""                 . 16463 1157300 10121.15 3647.27
. ""                 . 16464 1159387 10380.15 4451.18
. ""                 . 16467 1169550 10360.23 4246.16
end
format %tdnn/dd/CCYY DateAnnounced
format %tdNN/DD/CCYY Date

The variables MV_A, A_ISIN and DateAnnounced (first three variables) belong together and are one observation.
Basically, what Stata should do is taking the Date from the "DateAnnounced" variable, matching it with the correct Date in the "Date" variable and then returning the value of the variable with the name which matches the A_ISIN observation.
So for example the first observation of MV_A, the A_ISIN "AU000000FGL6" with the DateAnnounced "1/17/2005" should get the value"10698.81" assigned, which is the value of the variable AU000000FGL6 on the day 1/17/2005.
Observations in MV_A where there's no variable matching the A_ISIN observation can stay empty (e.g. DK0010207497).

Hope it is understandable.

Would greatly appreciate help from anyone of you!

Best
Andi

[Joro Kolev]

Is this not what vlookup does? Stata has a user written -vlookup- command.

[Broline Sagini]

Do you have an ID variable? if you do, the easier part would be splitting the dataset into 2 parts, one with the first date and the other with the second date then splitting the dataset into 2 and subsequently merging both datasets.

[William Lisowski]

Edit: removed post: didn't completely understand statement of the problem.

[Hemanshu Kumar]

Here is some code which does it, possibly not in the most efficient way:

local a_isin_vars JP3200450009 AU000000FGL6 AU000000TCL6

preserve
    keep Date `a_isin_vars'
    rename Date DateAnnounced
    tempfile matches
    save `matches'
restore

drop Date `a_isin_vars'
keep if !missing(A_ISIN)

merge m:1 DateAnnounced using `matches', keep(1 3) nogen

levelsof A_ISIN, local(a_isins)

gen wanted = .
foreach x of local a_isins {
    capture confirm var `x'
    if _rc != 0 continue
    replace wanted = `x' if A_ISIN == "`x'"
}

drop `a_isin_vars'

which produces:

. list, noobs ab(20)
  +------------------------------------------------+
  | MV_A         A_ISIN   DateAnnounced     wanted |
  |------------------------------------------------|
  |    .   AU000000FGL6       1/17/2005   10698.81 |
  |    .   DK0010207497       1/27/2005          . |
  |    .   AU000000TCL6       1/31/2005    4246.16 |
  |    .   JP3200450009       1/31/2005    1169550 |
  +------------------------------------------------+

note: in your data example, MV_A is always empty, and so that is what you find here. I don't know if you made some error in using the dataex command.

[Andi Smith]

Hi Hemanshu,

your code does exactly what i was looking for! Thank you very much!

Best
Andi

[Romalpa Akzo]

-reshape- could be a bit more convenient than -levelsof-.

*isid Date
tempfile OriginalData
save `OriginalData'
​​​​​​​
drop MV_A A_ISIN DateAnnounced
ren * V*
ren VDate DateAnnounced

reshape long V, i(DateAnnounced) j(A_ISIN) string

joinby using `OriginalData', unm(u)
drop _merge