BookmarkSubscribeRSS Feed
Sullivan
Calcite | Level 5

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.

 

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
...
SuryaKiran
Meteorite | Level 14

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 653 views
  • 0 likes
  • 3 in conversation