Matching two sets of dates

[Ama Perera]

Hi,

I have two sets of exchange rates (pex and tex), and the dates corresponding to these two sets of exchange rates is 'exdate'. However, I need to extract these two types of exchange rates only for the dates listed under 'secdate'.


* Example generated by -dataex-. To install: ssc install dataex
clear
input float(exdate pex tex) long secdate
13163 8.6456 1.5457 13172
13164 8.6667 1.5386 13173
13165 8.6695 1.5305 13177
13166 8.6543 1.5218 13178
13167 8.6405 1.5105 13179
13168 8.6405 1.5105 13180
13169 8.6405 1.5105 13181
13170 8.6499 1.5149 13184
13171 8.6526 1.514 13185
13172 8.6627 1.513 13186
13173 8.6484 1.5147 13187
end
format %td exdate
format %d secdate


I tried using 'merge' but it didn't work. I appreciate if someone could advise me on how to achieve this.

Thank you

Ama

[Mike Lacy]

-merge- would be relevant if your pex and tex variables were in different files, but the example you show has all the data in one file, so I'm confused about what your data example is supposed to represent. The term "extract" here is hard for me to understand here, as it usually indicates that one wants to make a separate file with only a selection of variables, but that seems not to be relevant here, so at least for me, you'll need to explain/illustrate what you have in mind by "extract." Also "didn't work" does not tell us enough about the problem for us to help you; we don't have any way to know the particular way in which it did not work. You need to show us exactly what you typed and what Stata responded. Without this information, it's quite hard to help you, but with this information, it's likely easy.

[Joseph Coveney]

Same as Mike, I'm not sure why you have everything in one dataset. In order to use merge, you'll need to separate the two date variables into separate datasets. One way to do this is illustrated below.

.ÿversionÿ15.1

.ÿ
.ÿclearÿ*

.ÿ
.ÿquietlyÿinputÿintÿexdateÿdouble(pexÿtex)ÿintÿsecdate

.ÿformatÿ%tdCY-N-Dÿ*date

.ÿ
.ÿ*
.ÿ*ÿBeginÿhere
.ÿ*
.ÿpreserve

.ÿ
.ÿcontractÿsecdate,ÿfreq(discard)

.ÿdropÿdiscard

.ÿ
.ÿrenameÿsecdateÿexdate

.ÿ
.ÿtempfileÿsecdates

.ÿquietlyÿsaveÿ`secdates'

.ÿ
.ÿrestore

.ÿmergeÿm:1ÿexdateÿusingÿ`secdates',ÿkeep(match)ÿnogenerateÿnoreport

.ÿ
.ÿlistÿexdate-tex,ÿnoobsÿseparator(0)

ÿÿ+------------------------------+
ÿÿ|ÿÿÿÿÿexdateÿÿÿÿÿÿpexÿÿÿÿÿÿtexÿ|
ÿÿ|------------------------------|
ÿÿ|ÿ1996-01-24ÿÿÿ8.6627ÿÿÿÿ1.513ÿ|
ÿÿ|ÿ1996-01-25ÿÿÿ8.6484ÿÿÿ1.5147ÿ|
ÿÿ+------------------------------+

.ÿ
.ÿexit

endÿofÿdo-file


.

If you have a reasonably limited number of SEC dates, then you could use a command, such as levelsof, that would allow keeping the one dataset in-place. Something like

quietly levelsof secdate, local(exdates)
generate byte keep = 0
foreach exdate of local exdates {
    quietly replace keep = 1 if exdate == `exdate'
}
keep if keep
drop keep secdate

[Ama Perera]

Mike Lacy

Thanks for the reply and sorry for any confusion.

What I really need to do, in other words, is matching the 'pex' and 'tex' data (which originally fall under 'exdate') according to 'secdate'. Originally downloaded exchange rate data are as follows.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(exdate pex tex)
13163 8.6456 1.5457
13164 8.6667 1.5386
13165 8.6695 1.5305
13166 8.6543 1.5218
13167 8.6405 1.5105
13168 8.6405 1.5105
13169 8.6405 1.5105
13170 8.6499 1.5149
13171 8.6526 1.514
13172 8.6627 1.513
13173 8.6484 1.5147
end
format %td exdate

The dates that I need to match the pex and tex variables are (secdate);

* Example generated by -dataex-. To install: ssc install dataex
clear
input long secdate
13172
13173
13177
13178
13179
13180
13181
13184
13185
13186
13187
end
format %d secdate

Thanks.

[Ama Perera]

Joseph Coveney

Thanks a lot for the reply. The 'levelsof 'command worked perfectly!