DATA Step, Macro, Functions and more

One to many merge mystery

Reply
Occasional Learner
Posts: 1

One to many merge mystery

Capture.PNGallrat pic.PNGTaxrate pic.PNG

Hello,

I am a frequently perform one to many merges without any trouble, I'm really confused why this current attempt isn't working correctly.

 

I have two data sets (Taxrate17B and AllRat17) and I'm trying to match them on 4 variables (Pol_unit, Levy_Name, LevyYear, and GrossRate).  One data set (Allrat17) includes three additional variables (Resagrate, CommIndRate, and qual). The second data set contains two additional variables (code & voted).  My end goal is a data set where each observation for all ten variables.  There is no missing data.

 

This is the language I'm using:

Data TaxRate17B; set work.TaxRate17B;

Proc sort; by pol_unit Levy_Name LevyYear GrossRate;

Data Allrat17; set work.Allrat17;

Proc sort; by pol_unit Levy_Name LevyYear GrossRate ;

Data SchoolRate17; Merge AllRat17 TaxRate17B;

by pol_unit Levy_Name LevyYear GrossRate;

Run;

 

My result is SchoolRate17 in the first photo. I'm really at a loss why the merge is working well for some observations (see lines 3,4,5,8,11, and 12) but not the others. For some observations, the LevyYear is '.' but I don't think that's the problem.

 

Any suggestions are appreciated! Thank you.

 

Super User
Super User
Posts: 9,840

Re: One to many merge mystery

I would suspect that one of your merge variables isn't what you think it is, possibly a format attached or a very small difference (probably gross rate).  Really hard to say, post test data in the form of a datastep - this is the only way we can see data and structure as pictures don't.  You can follow this post:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

Also, code formatting is very important!  That block of code is impossible to read properly, some simple changes - first using the code window (which is the {i} above the post area):

data work.taxrate17b;
  set work.taxrate17b;
run;

proc sort data=work.taxrate17b; 
  by pol_unit levy_name levyyear grossrate;
run;

data work.allrat17;
  set work.allrat17;
run;
...
PROC Star
Posts: 631

Re: One to many merge mystery

Check your numeric values. Format may make them look like same values but actually not.

 

For example if you have a numeric values 1.55555 and 1.55000 which are formatted to 3.2 then you can see these values as 1.55 which make them look like equal but, behind they are two different values.

 

Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 2 replies
  • 86 views
  • 0 likes
  • 3 in conversation