One to many merge by name

[Peppermint]

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!

[ballardw]

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.