thank you for your reply.
to elaborate it little more, here I am trying to explain it with a simple example. Please let me know if it is easy to understand now.
Let's say I have an incoming data for a set of employees where I have data like below:
table1
id1
id2
dob
name
309
6789
07/08/1987
abc
567
8905
07/09/1966
xyz
348
1235
07/10/1967
cdf
456
5688
07/10/1969
vbn
Then I need to perform a comparison if any of the combination of id1 and id2 already exist in any of employees department (sales, marketing ,payroll and there could be more etc). I will use id1 and id2 combination to pull what all department they exist.
My sales dept data is like below:
id1
id2
dob
name
309
6789
07/08/1987
abc
567
8905
07/09/1966
xyz
348
1235
07/10/1967
efg
Marketing dept data is like below:
id1
id2
dob
name
309
6789
07/08/1987
abc
567
8905
07/09/1966
xyz
348
1235
cdf
Payroll dept data is like below:
id1
id2
dob
name
309
6789
07/08/1987
abc
567
8905
07/09/1966
xyz
Now when I want get a comparison of all 4 datasets, I merged all 4 by id1 and id2. And I am renaming column names as _<dept name> , see below green bold names.
data all;
merge &table_list;by id1 id2;
run;
where table_list is macro variable that contains all table names like sales, marketing and payroll etc.
id1
id2
dob
name
dob_sales
name_sales
dob_marketing
name_marketing
dob_payroll
name_payroll
309
6789
07/08/1987
abc
07/08/1987
abc
07/08/1987
abc
07/08/1987
abc
567
8905
07/09/1966
xyz
07/09/1966
xyz
07/09/1966
xyz
07/09/1966
xyz
348
1235
07/10/1967
cdf
07/10/1967
efg
cdf
So now since 348( id1) and 1235(id2) combination does not exist in payroll dataset , so I would like update dob_payroll and name_payroll it as 'id1 and id2 combination does not exist'.
If we know the number of datasets we are merging, we can assign alias like @ballardwmentioned . But here every time the number and name of datasets in merge statement will change it could be 5 or 10 or 20 ,based on incoming/existing data.
Please let me know if this helps to understand the problem statement.
... View more