How to merge data sets with a common variable?

Here is the simple way of merging the data sets with a common variable if the datasets has the same prefix name.

For example: col1-col10, dsn1-dsn 7 , or data1 to data10 with common variable of ID.

Considering we have 10 datsets and all of them having the same prefix data;

%macro mymerge (n);

data merged;
merge %do i = 1 % to &n;
data&i %end; ; /* this additional ';' is necessary, the first ';' is for the "%end", while the second ';' is for "Merge"*/;
by id;
run;
%mend;
%mymerge(10)

Merging the data sets using macro code

Merging the data sets with a common variable if the datasets has the same prefix name?

For example: col1-col10 dsn1-dsn 7 data1 to data6 with common variable of Usubjid.

here is the example, I have 7 datasets i need to merge and each of them having the common variable(usubjid) to merge, and all the datasets having the same prefix dsn(dsn1 to dsn7).

%macro allmerge (n);
data combine;
merge
%do i = 1 % to &n;
dsn&i
%end;
; /* this additional ';' is necessary, the first ';' is for
the "%end", while the second ';' is for "Merge" */
by usubjid;
run;
%mend;
%allmerge (7)

Resolving and Using &&var&i Macro Variables

Here are the 2 important questions always comes up in our minds,(& vs && vs &&& and single dot and double dots) when we are dealing with macros for the first time and here are the answers for them.

I did find a very good regarding the above topics in the one of the SAS forums and IAN WHITLOCK explained it very clear.

direct link :

or

when to use &,&&,and &&&,how do we distinguish?

&MACVAR references a macro variable. The rule is that the scanner reads from left to right. Two ampersands resolve to one and scanner continues. If the resulting expression has ampersands then that expression is scanned again. So &&x resolves to &x resolves to value of X

&&&x resolves to & followed by the value of X which then resolves to the value of the variable indicated.

If you are familiar with TSO CLISTS (even if you are not), they are very similar to SAS macro. SAS was originally based on PL1, so both SAS syntax and SAS macro are similar in some ways to

PL1 and PL1 macro.

what is the diff between Single dot and double dot(eg. &chech.> and &check..)

&CHECK and &CHECK. are the same thing. If the scanner finds a . that ends the macro variable reference, then the scanner eats the. and ends the reference. If there is no current macro variable then a . is a . So if &X is MYFMT then &X.. is MYFMT.

Test example: what is &&&X..5 when X has the value V and V has the value TEST?

&&&X..5 -> &V.5 -> TEST5

Test: Consider &&&...&X where the dots indicate there are a total of 15 ampersands preceding the X. Write a sequence of %LET statements followed by

%put &&&...&X ;

so that this causes text to be written on the log without any notes, warnings. or errors. One, three, and 15 are interesting sequences of amprsands. Find one number in between 3 and 15 and next oone after 15 that are interesting for the same reason. How many dots would be needed to make the letter S immeadiately follow the value of &&&...&X?

You may also need to check the SIGI paper to understand the multiple ampersands concept in detail:

Example: ....

%let dsn=study;
%let n=05;
%let dsn05=Client; %put &dsn&n; *Resolves to study05;

%put &dsn.&n; *Resolves to study05;

%put &dsn..&n; *Resolves to study.05;

*Multi Ampersands Concept; %put &&dsn&n;

* First Scan Resolves to &dsn05;
* Second Scan Resolves to Client;

%put &&&dsn&n;

* First Scan Resolves to &client05;
* Second Scan Resolves to &Client05; *with an error message in the log file....;

*Log file; 57 %put &&&dsn&n;

WARNING: Apparent symbolic reference STUDY05 not resolved.

&study05

/*Because.. the most common mistake is that.. */
/*We assume that macro variable resolution process proceeds from right to left...*/
/*Infact it isn't. */

The bottom line is ...

/**For some reason.... the &&&dsn&n is taken as && &dsn&n, which resolves to &client05.*/
/*This macro variable will not get resolved, because &client5. macro variable isn't there in the symbol table.; */

Macrovariables and its resolution:

Example:

%let one=two;
%let two=three;
%let three=Check;

%put &one;
%put &&one;
%put &&&one;
%put &&&&one;
%put &&&&&one;
%put &&&&&&one;
%put &&&&&&&one;
%put &&&&&&&&one;
%put &&&&&&&&&one;
%put &&&&&&&&&&one;

Answer:

%put &one; two
%put &&one; two
%put &&&one; three
%put &&&&one; two
%put &&&&&one; three
%put &&&&&&one; three
%put &&&&&&&one; Check
%put &&&&&&&&one; two
%put &&&&&&&&&one; three
%put &&&&&&&&&&one; three

Resolving and Using &&var&i Macro Variables