One to many merge by name

Peppermint — Mon, 27 Apr 2015 08:29:17 GMT

Hi All

I read a lot of information about the merging process in SAS, but nothing helped so far. It should be an easy thing.

What I want to do:

- I have a table with information about customers

- I have a table with information about orders

--> the one thing in common they have, is a variable with information (firstname lastname). For the customers I concatenated two text variables.

Code:

/*Sort inputfile with info about customers, has the concatenated variable, one row per customer*/

proc sort data=ECom.kunden;

by RechnungAn;

run;

/*Sort inputfile with info about orders, several rows possible for one customers*/

proc sort data=ECom.bestellungen;

by RechnungAn;

run;

/*do the actual merge*/

data ECom.AlleDaten;

merge ECom.kunden ECom.bestellungen;

by RechnungAn;

run;

/*show all*/

proc print data=ECom.AlleDaten;

run;

The result is one table, but no real merging all information is separately. I would like to have the infos customer - orders, meaning for each order I would like to have more information that is stored at the customer (e.g. address).

I could imagine that there is some issue if the variables RechnungAn don't have the same length maybe? Or that the concatenating process messes something up?

Thanks a lot for your help!

Re: One to many merge by name

ballardw — Mon, 27 Apr 2015 15:13:37 GMT

1) relying on Name is poor practice because you may get different people with the same name in you customer base, the more customers the more likely. 2) The same person might appear in your database with two names Robert Smith, Rob Smith, or Bob Smith could all be the same person.

But this would be a place to start.

proc sql;

create table Ecom.AlleDaten as

select a.*, b.*

from ECom.bestellungen as a left join ECom.kunden as b

on a.RechnungAn=b.RechnungAn;

quit;

Better could be to use :

on a.lastname=b.lastname and a.firstname=b.firstname

As you depending on how you created the RechnungAn variable you might have concatenated names like "Bob Smith" and "Bob Smith" with one and 2 spaces between names and those wouldn't match. Also different lengths might cause a problem comparing "Bob Smith" to "Bob Smit"

If you have a customer identification that would be preferable to names.

topic One to many merge by name in SAS Programming

One to many merge by name

Re: One to many merge by name