Hi:
It's not really a special merge. It's just a merge. Of course, you first have to read your XLS data into a SAS dataset. That's going to require PROC IMPORT or the Excel LIBNAME engine.
So first, I made some data using SAS. In the second step, I called the file PRETEND_XLSDATA -- you would not have EITHER of these steps in your code. But I needed to have some data for the MERGE. Look at the data I made. I included a row in each file that does not exist in the other file, because it's important to consider how you want this situation handled in the output.
[pre]
** make some test data;
data sasdata;
infile datalines;
input a b;
return;
datalines;
1 2
2 4
3 6
5 7
;
run;
** make some data that LOOKS like the data described from Excel;
data pretend_xlsdata;
infile datalines;
input a b newvar;
return;
datalines;
1 2 67
2 4 89
3 6 34
8 3 99
;
run;
[/pre]
Now, all it takes is a DATA step program with a MERGE. When merging, it's always best to use BY variables. You could have done this in an SQL join, too. But you can only create 1 file with each SQL query...and I wanted to illustrate the various possibilities with the MERGE.
I'm going to create 4 different files, by testing the IN= variables for each dataset.The IN= variables are a "flag" or signal about which variables are in which dataset in the MERGE. So, for example, if an observation is coming from the SASDATA data set, then the value of the variable INSAS will be set to 1 (otherwise, it will be 0). If an observation is coming from the PRETEND_XLSDATA file, then the value of INXLS variable will be set to 1 or else it will be 0. These are called Boolean variables where 1=yes and 0=no. So I can have this test:
IF INSAS is the same as IF INSAS=1 then...
IF INXLS is the same as IF INXLS=1 then ...
IF INSAS and NOT INXLS is the same as IF INSAS=1 and INXLS=0 then ...
[pre]
** perform the merge;
** show possible combinations of output tables;
** based on testing the IN= variable;
data makeall inboth sasonly xlsonly;
merge sasdata(in=insas)
pretend_xlsdata(in=inxls);
by a b;
if insas and inxls then output inboth;
if insas or inxls then output makeall;
if insas and not inxls then output sasonly;
else if inxls and not insas then output xlsonly;
run;
** PRINT each file;
ods listing;
proc print data=makeall;
title 'ALL observations from both files';
title2 'note how A=5 and B=7 and A=8 and B=3 obs were included';
run;
proc print data=inboth;
title 'A and B were the same in both files';
title2 'note how A=5 and B=7 and A=8 and B=3 obs were NOT included';
run;
proc print data=sasonly;
title 'Obs that were in SASONLY file but not XLS';
title2 'Good for error checking';
run;
proc print data=xlsonly;
title 'Obs that were in XLS file but not SAS';
title2 'Good for error checking';
run;
[/pre]
If you have duplicate values of A and B in both files, then a MERGE might not be the best technique to use. For more help with this problem, your best bet is to contact Tech Support so they can help you with the best solution for your particular data and your particular merge requirements.
cynthia
Output from the PROC PRINTS:
[pre]
ALL observations from both files
note how A=5 and B=7 and A=8 and B=3 obs were included
Obs a b newvar
1 1 2 67
2 2 4 89
3 3 6 34
4 5 7
5 8 3 99
******************************************************
A and B were the same in both files
note how A=5 and B=7 and A=8 and B=3 obs were NOT included
Obs a b newvar
1 1 2 67
2 2 4 89
3 3 6 34
******************************************************
Obs that were in SASONLY file but not XLS
Good for error checking
Obs a b newvar
1 5 7
******************************************************
Obs that were in XLS file but not SAS
Good for error checking
Obs a b newvar
1 8 3 99
[/pre]