topic Merging two datasets in SAS Data Management

Merging two datasets

Alberto_Alvarez — Wed, 20 Mar 2019 19:03:26 GMT

Dear SAS Users,

I need to merge two datasets. In particular, I need to merge "return" data to "link" such that the "date" in "return" is between "LINKDT" and "LINKENDDT" in "link" data. For this I use the code below:

proc sql;
create table comp2 as
select a.*, b.fyrc, b.gvkey
from return as a, link as b
where a.permno=b.lpermno and b.linkprim in ('P', 'C') and
b.LINKTYPE in ('LU', 'LC') and
a.date>= b.LINKDT and (a.date <= b.LINKENDDT or missing(b.LINKENDDT))
order by gvkey, date;
quit;

The result that I get is that I have a merge for "gvkey" = 7701. Even though in the "link" it has "LINKDT" and "LINKENDDT " as 31-Jan-67 29-Dec-67 and 01-Jan-68 03-Jan-67. Basically, I should not be merged. Can you, please help me to resolve this issue? Why do this firm has a merge? I attach two datasets that I use (a sample)

Re: Merging two datasets

ballardw — Wed, 20 Mar 2019 22:46:25 GMT

Problem: Your Linkenddt is a datetime variable and Linkdt is a date. So the two values do not use the same units of measure. Dates are number of days since 01JAN1960 and datetimes are numbers of seconds from 01Jan1960:00:00:00.

Just opening your data set link it shows linkenddt values like : 29DEC67:00:00:00 so your post didn't pay any attention to the format of the actual values in the set.

By the way, you might want to watch this behavior when reading from popular Microsoft data sources. Many of them include a no hours, minutes or seconds time portion because the software is defaulting to date time storage and formats even though dates are intended. When all of the values for time are 00:00:00 you might consider trimming the values permanently using the datepart function.

To compare dates with datetimes you likely want to use

a.date <= datepart (b.LINKENDDT)