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!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.