topic Re: merge two data sets in macro using proc sql in SAS Programming

merge two data sets in macro using proc sql

panpan1215 — Wed, 01 Nov 2017 21:20:53 GMT

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.

Re: merge two data sets in macro using proc sql

SAS_inquisitive — Wed, 01 Nov 2017 21:31:12 GMT

%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

Re: merge two data sets in macro using proc sql

panpan1215 — Wed, 01 Nov 2017 22:06:50 GMT

Thanks for the reply but it still gives me errors.

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

Re: merge two data sets in macro using proc sql

ballardw — Wed, 01 Nov 2017 22:13:46 GMT

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

Re: merge two data sets in macro using proc sql

panpan1215 — Wed, 01 Nov 2017 22:41:40 GMT

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 SAS_inquisitive's suggestion to write the macro. Please see reference above. Thanks

Re: merge two data sets in macro using proc sql

ballardw — Wed, 01 Nov 2017 22:59:45 GMT

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.

Re: merge two data sets in macro using proc sql

Tom — Wed, 01 Nov 2017 23:12:02 GMT

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;

Re: merge two data sets in macro using proc sql

Kurt_Bremser — Thu, 02 Nov 2017 07:00:13 GMT

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.

Re: merge two data sets in macro using proc sql

panpan1215 — Thu, 02 Nov 2017 16:31:20 GMT

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

Re: merge two data sets in macro using proc sql

panpan1215 — Thu, 02 Nov 2017 16:55:58 GMT

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.

Re: merge two data sets in macro using proc sql

SAS_inquisitive — Thu, 02 Nov 2017 17:33:58 GMT

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

like select a.variable,

a is alias for that data set .

Re: merge two data sets in macro using proc sql

Kurt_Bremser — Thu, 02 Nov 2017 20:02:18 GMT

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