topic Re: Unable to do a horizontal few-to-many merge in SAS Programming

Unable to do a horizontal few-to-many merge

Nietzsche — Sun, 09 Oct 2022 03:01:12 GMT

Hi I am following a textbook (Elliot, SAS Essentials pg 99) example on a Few to Many merge using MERGE statement. Screenshot of the textbook example is below.

I opened the SAS file and completed the code as shown in the example

data sales;
format item $20. buyertype $8.;
input item buyertype price;
datalines;
CARBCLEANER REPAIR 2.30
BELT CONSUMER 6.99
MOTOROIL CONSUMER 14.34
CHAIN STORE 18.99
SPARKPLUGS REPAIR 28.99
CLEANER CONSUMER 1.99
WRENCH STORE 18.88
;
run;
proc print data = sales; run;


data discount;
format buyertype $10.;
input buyertype discount;
datalines;
consumer 0
repair .33
store .4
;
run; 
proc print data=discount; run;


proc sort data=sales; by buyertype; run;
proc sort data=discount; by buyertype; run;


data report;
	Merge sales discount; by buyertype;
	final = round(price*(1-discount),.01);
	run;
	
proc print data=report;
sum final;
run;

I must have done something wrong because instead of getting a horizontal merge as shown in Table 4.14 above, instead I got a vertical merger like this:

what did I do wrong in my code that cause SAS to produce a vertical merge? In the textbook to get vertical merge, they used the SET statement and MERGE statement is for horizontal merge.

Any help would be greatly appreciated.

Re: Unable to do a horizontal few-to-many merge

Kurt_Bremser — Sun, 09 Oct 2022 03:07:36 GMT

The strings must be an exact match for the BY to work. "REPAIR" is not equal to "repair".

Use the UPCASE function to convert the lowercase values to uppercase.

Re: Unable to do a horizontal few-to-many merge

Tom — Sun, 09 Oct 2022 07:06:15 GMT

Looks it did what you asked. But since none of the values of BUYERTYPE matched you did not get what you wanted.

If that is the code you used the easiest thing to fix is make the values in the DISCOUNT dataset also be in uppercase so that your merge will actual finds some values that match.

You could just fix the in-line data to use uppercase letters.

data discount;
  input buyertype :$10. discount;
datalines;
CONSUMER 0
REPAIR .33
STORE .4
;

You could also just read the values using the $UPCASE informat.

data discount;
  input buyertype :$upcase10. discount;
datalines;
consumer 0
repair .33
store .4
;

Or you could change the values you already have by using the UPCASE() function.

data discount;
  input buyertype :$10. discount;
  buyertype = upcase(buyertype);
datalines;
consumer 0
repair .33
store .4
;

Note that what you are doing is called a one-to-many merge. You want to match one observation from one dataset to many observations from another. And that is what your example code is doing. This only one observation per BUYERTYPE in the DISCOUNT dataset.

The term "few-to-many merge" seems to imply that you want to merge more than one observations from both sources. That is called a many-to-many merge. You cannot (at least without a lot of extra coding) do a many-to-merge with a data step.

Are those terms "horizontal merge" and "vertical merge" what is used in that book?

I assume by "horizontal merge" they just mean a merge.

I assume that by "vertical merge" they mean concatenating the data using a SET statement with multiple dataset. That is not really merging the data in any sense. But perhaps they mean interleaving the data by using a SET statement with a BY statement. That at least has the potential to intermix the observations from the source dataset instead of just appending them together so perhaps you could consider it a merge of some sort.