topic Re: Match data with different conditions in SAS Programming

Match data with different conditions

Songchan — Wed, 05 Sep 2018 18:38:05 GMT

Hello,

I matched two data sets using one condition (cusip number), now I'm going to match the lost matched observations with another condition (ticker number) I don't know how.

/*merge by cusip*/

proc sql;
create table cc as
select a.*,b.*
from aa02 as a, bb as b
where a.COL3=b.cusip and -5<=a.tr-b.datadate<370;
quit;

aa02 (891201 observations) is one dataset, bb is another one, and there are only 140110 observations matched.

Re: Match data with different conditions

PeterClemmensen — Wed, 05 Sep 2018 19:31:58 GMT

So you want to match the 891201-140110 non matched observations with observations from a third data set or?

If not, please be more specific.

Re: Match data with different conditions

Songchan — Wed, 05 Sep 2018 19:41:35 GMT

Hello,

I want to matched the rest observations (891201-140110) in aa02 data set with bb data set. Because what I want to do is to match the two data sets with CUISP number, Ticker number and Company Names, I just finished matching with CUISP number, and only 140110 out of 891201 are matched, that is not a good match.

Re: Match data with different conditions

PGStats — Thu, 06 Sep 2018 04:46:46 GMT

"I want to do is to match the two data sets with CUISP number, Ticker number and Company Names"

that doesn't (shouldn't) require separate steps. It can all be done in one operation.

What makes you expect to get more matches?

Re: Match data with different conditions

Songchan — Thu, 06 Sep 2018 14:17:03 GMT

Hello,

I have two data sets, one is monthly data, the other is annual data, I need to combine these two to ensure that each company has its both monthly and annual data. The first condition is CUSIP number, and the rest observations of monthly data match annual data under the second condition of same ticker number, there also should be some observations not matched, so the last condition is company name.