DATA Step, Macro, Functions and more

merge two data sets in macro using proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

merge two data sets in macro using proc sql

Hi all! I'm write a macro to merge two datasets by their ID. Each subject has multiple IDs, so the variable name has to be passed as an argument in the macro. Here's my code:

%macro merge(data1 =, data2 =, ID =, new=, );
	proc sql;
	create table &new AS	
		Select *
		from &data1 as a Full join &data2 as b on &data1&ID = &data2&ID;
quit;
run;
%mend

May I know what went wrong? Thanks for the help. 

 


Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 7,074

Re: merge two data sets in macro using proc sql

[ Edited ]
Posted in reply to panpan1215

You have too many commas in the %MACRO statement.

You are missing a semi-colon after the %MEND statement.

You have an unneeded/unwanted RUN statement.

But your biggest mistake is trying to use the macro variables as the dataset aliases in your ON clause instead of the A and B aliases that specifically created in the FROM and FULL JOIN clauses.  The whole purpose of added the AS A and AS B to the query is you can use A. and B. to tell SQL which dataset the variable you are referencing is coming from.

 

%macro merge
(data1=
,data2=
,ID=
,new=
);
proc sql;
create table &new AS	
  select *
  from &data1 as a 
  full join &data2 as b 
    on a.&ID = b.&ID
;
quit;
%mend merge;

View solution in original post


All Replies
Super Contributor
Posts: 271

Re: merge two data sets in macro using proc sql

[ Edited ]
Posted in reply to panpan1215
%macro merge(data1 =, data2 =, ID =, new=, );
	proc sql;
	create table &new AS	
		Select *
		from &data1 as a Full join &data2 as b on a.&ID = b.&ID;
quit;
run;
%mend
Occasional Contributor
Posts: 8

Re: merge two data sets in macro using proc sql

Posted in reply to SAS_inquisitive

Thanks for the reply but it still gives me errors. 

 

" ERROR: The following columns were not found in the contributing tables"

Super User
Posts: 11,343

Re: merge two data sets in macro using proc sql

Posted in reply to panpan1215

panpan1215 wrote:

Thanks for the reply but it still gives me errors. 

 

" ERROR: The following columns were not found in the contributing tables"


Was that the complete error or did it list the actual variables (Strongly suspect Yes)

This error message is very clear: your code is referencing something in the position SAS expects a variable and the value you supply is not actually a variable in the data set.

 

Post the log including the SQL and the error after running :

 

options mprint symbolgen;

%merge (<your parameters go here>);

 

Since the only explicitly mentioned variable name is in the macro parameter ID that is the likely problem: the value you supplied is not an actual variable in both sets.

 

Occasional Contributor
Posts: 8

Re: merge two data sets in macro using proc sql

Okay and thanks. 

 

My invoke the following code:

 

%merge(data1 = Mainp, data2 = Cross, ID = siteID, new= test);

 

And it displays

ERROR: The following columns were not found in the contributing tables: CrossiteID, MainsiteID.

If you notice that my two datasets are called Cross and Main. Some how the macro concatenate data name and variable name.  I used 

Super User
Posts: 11,343

Re: merge two data sets in macro using proc sql

Posted in reply to panpan1215

And where is the log generated with the MPRINT option?

 

Unfortunately just because you say you used @SAS_inquisitive's suggestion we cannot verify that you spelled everything correctly.

The log would show us that.

Your error message strongly suggests that you used

from &data1 as a Full join &data2 as b on &data1&ID = &data2&ID;

 

from your original code

and not the suggested

from &data1 as a Full join &data2 as b on a.&ID = b.&ID;

 

Perhaps you had an error on compiling and it did not replace the original macro that failed.

Occasional Contributor
Posts: 8

Re: merge two data sets in macro using proc sql

I'm new to sas community and I will be more mindful of posting all the relevant information the next time. Thanks.

Super User
Posts: 7,846

Re: merge two data sets in macro using proc sql

Posted in reply to panpan1215

Keep in mind that your SQL is sloppy programming. By using

select *

and assuming the same key variable in both datasets, you will always get a WARNING from the SQL, and a batch job including that macro will always end with a minimum return code of 1. Since you'll be forced to ignore a return code 1 (or a WARNING in the EG/SAS Studio log), you will sooner or later miss another WARNING that should be of interest for you.

It's also fact that schedulers consider any non-zero return code as a problem and flag your job as defective.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: merge two data sets in macro using proc sql

Posted in reply to KurtBremser
Hi! I did notice whenever I write code about join, there's always a warning about some variable already exist in the dataset. Would you mind showing me how to write this without generating warning message? Thanks.
Super Contributor
Posts: 271

Re: merge two data sets in macro using proc sql

Posted in reply to panpan1215

You have to select  that variable from either one of the data sets.

 

     like select a.variable,

     a is alias for that data set .

Super User
Posts: 7,846

Re: merge two data sets in macro using proc sql

Posted in reply to panpan1215

panpan1215 wrote:
Hi! I did notice whenever I write code about join, there's always a warning about some variable already exist in the dataset. Would you mind showing me how to write this without generating warning message? Thanks.

That is a side-effect of using the asterisk in select. If variables with the same name are present in both datasets, only one will be taken, and a WARNING given because of the possible loss of information. The way to avoid this is to explicitly name all variables that shall be included in the output.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
3 weeks ago
Super User
Super User
Posts: 7,074

Re: merge two data sets in macro using proc sql

[ Edited ]
Posted in reply to panpan1215

You have too many commas in the %MACRO statement.

You are missing a semi-colon after the %MEND statement.

You have an unneeded/unwanted RUN statement.

But your biggest mistake is trying to use the macro variables as the dataset aliases in your ON clause instead of the A and B aliases that specifically created in the FROM and FULL JOIN clauses.  The whole purpose of added the AS A and AS B to the query is you can use A. and B. to tell SQL which dataset the variable you are referencing is coming from.

 

%macro merge
(data1=
,data2=
,ID=
,new=
);
proc sql;
create table &new AS	
  select *
  from &data1 as a 
  full join &data2 as b 
    on a.&ID = b.&ID
;
quit;
%mend merge;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 164 views
  • 0 likes
  • 5 in conversation