BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
panpan1215
Fluorite | Level 6

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. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

11 REPLIES 11
SAS_inquisitive
Lapis Lazuli | Level 10
%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
panpan1215
Fluorite | Level 6

Thanks for the reply but it still gives me errors. 

 

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

ballardw
Super User

@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.

 

panpan1215
Fluorite | Level 6

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 

ballardw
Super User

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.

panpan1215
Fluorite | Level 6

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

Kurt_Bremser
Super User

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.

panpan1215
Fluorite | Level 6
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.
SAS_inquisitive
Lapis Lazuli | Level 10

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

 

     like select a.variable,

     a is alias for that data set .

Kurt_Bremser
Super User

@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.

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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