Month: January 2017

SAS: Join or Merge Tables

Note: Be careful to use merge if the the byvar are not unique in both tables, especially when the vars are numeric and can be aggregated .

Compare of SQL method and merge method

Inner Join

SELECT var
FROM table1 as a
INNER JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);
by var;
if a and b;

Left Join

SELECT var
FROM table1 as a
LEFT JOIN as b
ON a.var=b.var; 

merge table1 (in=a) table2;
by var;
if a;

Left Join exclude Inner Join

SELECT var
FROM table1 as a
LEFT JOIN table as b
ON a.var=b.var;
WHERE b.var IS NULL;

merge table1 (in = a) table2 (in=b);
by var;
if a and not b;

Right Join

SELECT var
FROM table1 as a
RIGHT JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);
by var;
if b;

Right Join exclude Inner Join

SELECT var
FROM table1 as a
RIGHT JOIN table as b
ON a.var=b.var

WHERE a.var IS NULL;

merge table1 (in = a) table2 (in=b);
by var;
if b and not a;

Full Join

SELECT var
FROM table1 as a
FULL OUTER JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);
by var;
if a or b;

or

merge table1 (in = a) table2 (in=b);
by var;

Full Join Inner Join

SELECT var
FROM table1 as a
FULL OUTER JOIN table2 as b
ON a.var=b.var;

merge table1 (in=a) table2 (in=b);

by var;
if a ne b; /* only work on 2 table merge*/

Macro: Check variable length, name, type of multiple tables with similar layout

/* set up list for the tables requied to scan for  the variables */

%let tlst = a b c d e f g;

/* Method 1: use parameter */

/* Generate vairable list for each table */

%macro varlen (sec=);
%let i = 1;
%do %while (%scan(&sec., &i, ‘ ‘) ne );
%let tbl= %scan(&sec., &i, ‘ ‘);
proc contents data = section&tbl._ug out=ug&tbl. (keep= MEMNAME name type length) noprint;
proc contents data = section&tbl._gr out=gr&tbl. (keep= MEMNAME name type length) noprint;
%let i = %eval (&i +1);
%end;
run;
%mend varlen;
%varlen (sec = &tlst.);

/* Method 2: use macro variable list directly */

/* Append varaible lists to one table */
%macro comp ;
%let i = 1;
%do %while (%scan(&tlst., &i, ‘ ‘) ne );
%let tbl= %scan(&tlst., &i, ‘ ‘);
proc append base = uglen  data = ug&tbl.;
run;
%let i = %eval (&i +1);
%end;
%let i = 1;
%do %while (%scan(&tlst., &i, ‘ ‘) ne );
%let tbl= %scan(&tlst., &i, ‘ ‘);
proc append base = grlen  data = gr&tbl.;
run;
%let i = %eval (&i +1);
%end;
proc sort data = uglen;
by name memname;
proc sort data = grlen;
by name memname;
run;
%mend comp;
%comp ;