Calculating the difference of each variables with all others

[Francois Durant]

Hi,

I would like to calculate the interest rate differential of each currency with all others. I need to do it in both directions: domestic-foreign and foreign-domestic. I have many currencies in the dataset and the currency sample will change in the future.

Here is a subset of my dataset along with an example of the output I would like to generate:

* want to do this for each of all pairs of currency with that exact variable names:
gen AUD_CAD = AUD-CAD
gen CAD_AUD = CAD-AUD

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Code double(AUD CAD CHF CNH CZK) float(AUD_CAD CAD_AUD)
17197 6.235  4.25  2.05     .  2.5 1.985 -1.985
17225 6.225  4.23  2.08     . 2.48 1.995 -1.995
17255  6.33 4.235  2.19     .  2.5 2.095 -2.095
17286  6.23 4.235  2.19     . 2.49 1.995 -1.995
17317 6.215 4.235  2.32     . 2.77  1.98  -1.98
17346 6.215 4.445  2.61     . 2.77  1.77  -1.77
17378   6.4  4.49 2.535     .    3  1.91  -1.91
17409  6.69  5.07 2.695     . 3.25  1.62  -1.62
17437  6.71  4.85  2.48     .  3.3  1.86  -1.86
17470 6.665  4.65  2.22     . 3.33 2.015 -2.015
17500   6.9  4.96  2.65     . 3.88  1.94  -1.94
17531   6.6  4.45 2.325     . 4.01  2.15  -2.15
17562  7.05  4.05   2.4     . 3.65     3     -3
17591  7.47   3.8  2.62     .  3.8  3.67  -3.67
17622 7.525  3.65 2.575     . 3.88 3.875 -3.875
17652   7.5   3.2  2.35     . 3.85   4.3   -4.3
17682  7.35   3.1  2.25     . 3.91  4.25  -4.25
17713 7.475  3.15  2.27     . 3.85 4.325 -4.325
17744  7.42  3.07  2.18     . 3.77  4.35  -4.35
17773  7.05  3.05   2.2     . 3.66     4     -4
17805  7.65  3.75  2.66     . 3.65   3.9   -3.9
17836  6.25  2.75     2     . 4.25   3.5   -3.5
17864 5.125  2.25  .765     . 3.76 2.875 -2.875
17897 4.525   1.8   .38     .  3.5 2.725 -2.725
17927 3.575  1.25   .71     . 2.35 2.325 -2.325
17955 3.375 1.075  .655     .  2.2   2.3   -2.3
17987 3.375  .695  .275     . 2.32  2.68  -2.68
18017 3.225  .595  .225     . 2.32  2.63  -2.63
18046  3.05   .35  .125     . 1.77   2.7   -2.7
18078 3.295  .345  .195     .  1.8  2.95  -2.95
18109   2.9    .3  .105     . 1.75   2.6   -2.6
18140     3  .295  .165     . 1.47 2.705 -2.705
18170  3.35  .295   .23     . 1.41 3.055 -3.055
18200 3.485   .28   .17     . 1.42 3.205 -3.205
18231  3.66    .4   .18     .  1.4  3.26  -3.26
18262  3.86  .285   .22     . 1.17 3.575 -3.575
18291  3.94   .33   .18     . 1.12  3.61  -3.61
18319   3.9   .25  .115     .    1  3.65  -3.65
18352  4.05    .3   .18     . 1.13  3.75  -3.75
18382  4.35   .33  .125     . 1.09  4.02  -4.02
18413   4.7  .475   .26     .  .82 4.225 -4.225
18443 4.625  .675  .105     .  .83  3.95  -3.95
18473  4.75  .875  .215     .  .78 3.875 -3.875
18505  4.76  .875  .175     .  .76 3.885 -3.885
18535  4.65 1.225  .115     .    1 3.425 -3.425
18564  4.55 1.175   .13     .  .83 3.375 -3.375
18596  5.12   1.3   .21     .  .85  3.82  -3.82
18627   4.8 1.075  .145     .  .78 3.725 -3.725
18658   4.9 1.075  .135     .  .82 3.825 -3.825
18686  4.85 1.075   .11     .  .67 3.775 -3.775
18717  4.72  1.05   .18     .  .66  3.67  -3.67
18746  4.75  1.05  .085     .   .7   3.7   -3.7
18778  4.75 1.255   .11     .  .85 3.495 -3.495
18808  4.75 1.035   .11     .  .75 3.715 -3.715
18837  4.75 1.035   .18     .  .85 3.715 -3.715
18870  4.77 1.035  -.02     .  .85 3.735 -3.735
18900  4.78 1.085   .31     .  .75 3.695 -3.695
18931  4.78 1.085   .37     .  .72 3.695 -3.695
18961 4.675 1.085    .3     .  .82  3.59  -3.59
18991 4.575 1.085   .13     . .955  3.49  -3.49
19023  4.57   1.1    .1     .  .76  3.47  -3.47
19052  4.57 1.355  .045     . .825 3.215 -3.215
19082   4.7 1.095  .045     . .795 3.605 -3.605
19113  4.37 1.225  .045     .  .82 3.145 -3.145
19144  3.97 1.275  .045     . .825 2.695 -2.695
19173  3.97 1.275     0     . .645 2.695 -2.695
19205  3.78  1.06 -.085     . .585  2.72  -2.72
19236 3.735  1.12 -.075     . .505 2.615 -2.615
19264 3.625  1.03 -.105     . .555 2.595 -2.595
19297  3.34  1.03 -.105     . .345  2.31  -2.31
19327 3.225  1.03   -.1     . .175 2.195 -2.195
19358  3.29 1.085  -.25     . .175 2.205 -2.205
19389 3.025 1.045 -.025     .   .2  1.98  -1.98
19417 3.055 1.115 -.045     .   .2  1.94  -1.94
19446 3.035 1.075 -.025     .  .19  1.96  -1.96
19478 3.025 1.105 -.025     .  .19  1.92  -1.92
19509 2.805 1.095  -.01     .  .19  1.71  -1.71
19537 2.875 1.075  -.05     .  .19   1.8   -1.8
19570  2.74 1.045   .01 2.638   .2 1.695 -1.695
19600 2.575 1.045  -.05 2.484   .2  1.53  -1.53
19631 2.575 1.025     0  2.24   .2  1.55  -1.55
19662 2.625 1.045  -.03 2.024   .2  1.58  -1.58
19691 2.465 1.035  -.03 2.021  .19  1.43  -1.43
19723 2.625 1.045  -.08 1.985  .18  1.58  -1.58
19754 2.605 1.075  -.08   2.1  .18  1.53  -1.53
19782 2.605 1.005  -.05 2.046  .18   1.6   -1.6
19813 2.585  .955  -.07 1.974  .18  1.63  -1.63
19843 2.655 1.025  -.05  1.98  .18  1.63  -1.63
19873 2.675 1.025  -.09 2.484  .14  1.65  -1.65
19904 2.775 1.045  -.08 3.197  .08  1.73  -1.73
19935 2.675 1.015  -.05 3.297  .08  1.66  -1.66
19964 2.735 1.045  -.02 3.034  .18  1.69  -1.69
19996 2.785 1.075 -.035 3.296  .18  1.71  -1.71
20027 2.725 1.115 -.025 3.294  .18  1.61  -1.61
20055 2.775  1.06 -.025 3.533  .18 1.715 -1.715
20088 2.975 1.075   -.2 4.042  .18   1.9   -1.9
20118 2.665  .825  -1.1 4.826  .18  1.84  -1.84
20146 2.405  .925  -.85 4.182  .18  1.48  -1.48
20178  2.52  .815 -.925 4.432  .18 1.705 -1.705
20208  2.37   .82 -.845 3.453  .08  1.55  -1.55
end
format %tdnn/dd/CCYY Code

Anyone can help? Thanks a lot in advance.

[Joro Kolev]

Check whether this below is not doing it:

. drop AUD_CAD CAD_AUD

. foreach i in AUD CAD CHF CNH CZK {
  2. foreach j in AUD CAD CHF CNH CZK {
  3. if `i'!=`j' gen double `i'_`j' = `i'-`j'
  4. }
  5. }

[Nick Cox]

Joro Kolev's code will I think work, but in principle it should be based on comparing variable names, not their values.

 
  if "`i'" != "`j'" gen double `i'_`j' = `i'-`j'

[Joro Kolev]

Excellent catch, Nick !

My code would work, but not for the reasons I was thinking, and the problem in my thinking is a lot more serious that Nick suggests: my code does not compare all the values of the variables, it just compares the first value of the variables (values in observation 1), as the example below illustrates:

. sysuse auto, clear
(1978 Automobile Data)

. gen price2 = price

. replace price2 = 1000000 in 2/l
(73 real changes made)

. if price==price2 dis "Say Nooooo"
Say Nooooo

So the problem in my thinking is that I all the time forget that the programmers -if- (what we did here) and the -if expression- (this thing we put at the end of our commands) qualifier work in very different ways. Here is the second construct:

. gen Noooo = 1 if price==price2
(73 missing values generated)

. summ Noooo

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
       Noooo |          1           1           .          1          1

In sum, my code would (probably?) work because in all likelihood the first observation of two different exchange rates are different.

What Nick did in his code, is to force variable name interpretation by putting the variable name in quotation marks "varname".

Joro Kolev's code will I think work, but in principle it should be based on comparing variable names, not their values.

if "`i'" != "`j'" gen double `i'_`j' = `i'-`j'

[Nick Cox]

There is an FAQ on this https://www.stata.com/support/faqs/p...-if-qualifier/

As I've often commented, in my view it's written backwards. The question that almost always is asked is

Why is my if command giving bizarre results?

and the answer is almost always

... because it is evaluating what is going in the first observation (and only that).

[Joro Kolev]

Thank you Nick for the FAQ of which I was not aware. I can dig out quite a few threads here on Statalist showing that quite a few other users get caught off guard by this behaviour, I participated in one or two of these threads myself.

I think the problem is that Stata manuals very fundamentally do not make a distinction between scalar and vector expressions. Hence in Stata manual we read about

if exp .... something comes here, and
something comes here... if exp

and they both look the same, but the first is scalar evaluation, and the second is vector evaluation, as I showed in the examples above in #4.


And a seemingly unrelated complaint: since when are these FAQs undated?

I recently tried to cite such a FAQ and I noticed that I could not find a date, and now same with the FAQ you pointed us to... On first occasion I just did not know how ti cite it. On this occasion, I am pretty sure that the FAQ you pointed us to is quite recent. I remember banging my head over this behaviour of -if- and not being able to find good answers. In fact I realised what is going on from some threads here on Statalist.

There is an FAQ on this https://www.stata.com/support/faqs/p...-if-qualifier/

As I've often commented, in my view it's written backwards. The question that almost always is asked is

Why is my if command giving bizarre results?

and the answer is almost always

... because it is evaluating what is going in the first observation (and only that).

[Francois Durant]

Many thanks to both of you Joro and Nick!

[Nick Cox]

I complained to StataCorp when FAQs were no longer dated explicitly and was told that some users were discounting older FAQs as likely to be out-of-date. Pfui to that was my private reaction: why should the scholarly be deprived because of a silly misconception?

A better reason might be that -- unless flagged otherwise as referring to older versions of Stata -- in principle all FAQs should remain relevant and correct when read, and so up-to-date.

Whenever I need to cite an FAQ I use search to find out the date. Alternatively, you may find the date commented out in the HTML source

I just found

October 2001; minor revisions February 2003, August 2005

in the code of one I wrote

https://www.stata.com/support/faqs/d...rue-and-false/

-- reassuring at least to me that it has stood for 15 years without revision.