topic Proc SQL merging problem possibly coding issue in SAS Programming

Proc SQL merging problem possibly coding issue

OzanKirtac — Wed, 15 Sep 2021 10:51:52 GMT

I am trying to merge two datasets but I seem to be missing something as the data I merge does not look really good. I try to add new variable to existing dataset on the condition that two variables have the same values in two different datasets. I attach partially both datasets. You will see when you merge, all data merges to the same observations. Log is clear so this possibly a coding problem. I did not attach the full datasets but partial datasets for crspmonthly2 and mergedcikedgar. The final output file is attached. I see that a one observation is matched with all the appended variables. Can be it be related with empty date cell in the data?

proc sql; create table perm.mergedcikedgar10 as select
a.*, b.gvkey
from perm.Mergedcikedgar as a left join perm.crspmonthly2 as b
on a.Cik=b.CIK
and month(a.date) eq month(b.datadate)
and year(a.date) eq year(b.datadate);
quit;

Re: Proc SQL merging problem possibly coding issue

Kurt_Bremser — Wed, 15 Sep 2021 11:06:16 GMT

Looks like there is only one match in your datasets that meets the conditions of the dates.

Re: Proc SQL merging problem possibly coding issue

ballardw — Wed, 15 Sep 2021 16:42:44 GMT

Equality for comparisons is very restrictive. So you may need to make sure the values are as you expect in multiple data sets. "ABC" does not match "Abc" or " ABC" (note leading space). You may need to make your comparison for character values case insensitive, such as UPCASE or LOWCASE both values, or make sure that there are no leading spaces or different numbers of embedded spaces ("A BC" is not equal to "A BC"), or that other characters separating values such as comma or hyphens are exactly the same in both sets. Also some times depending on your data source characters that look the same such as dash may be a different dash character.

Good luck.