topic Identifying common column (variable) names from multiple tables in SAS Programming

Identifying common column (variable) names from multiple tables

SasStatistics — Tue, 08 Jun 2021 17:39:42 GMT

Suppose I have two tables.

TableA contains the column names: Col1, Col2, Col3, Col 4, Col 5

TableB contains the column names: Col1, B2, Col3, B4

Now, I would like to create a function, taking arbitrary number of tables as input arguments and then returning the common columns of the tables used as input.

So for example:

MyFun(TableA, TableB) = Col1, Col3.

This would be a useful function when for instance looking for "keys" when working with joins in SQL. Does this kind of function exist or does anyone know how to code it?

Thanks.

Re: Identifying common column (variable) names from multiple tables

Reeza — Tue, 08 Jun 2021 17:54:14 GMT

Here's a rough example. You could convert it to a macro to achieve your full functionality.

https://gist.github.com/statgeek/95080ffddc5217af80248fd078dfbfeb

This may be useful as well, though likely overkill for what you're attempting.
https://gist.github.com/statgeek/3b57ae085d9f7a36a2d95c15f04e72e6

 /*Generate fake data to work with*/
	data class1;
		set sashelp.class;
		drop age sex;
	run;
	
	data class2;
		set sashelp.class;
		drop weight height;
	run;
	
	data class3;
		set sashelp.class;
		Order=1;
	run;
	
	data class4;
		set sashelp.class;
		keep name;
	run;
	
	data class5;
		set sashelp.class;
	run;
	
    /*Extract metadata from dictionary tables*/
	proc sql noprint;
		create table column_list as select memname, libname, name, type, 1 as count 
			from dictionary.columns where libname='WORK' and memname like 'CLASS%'
			/*ADD THE VARIABLE LIST HERE*/
			order by memname, name;
	quit;
	
    /*Transpose results to a more user friendly format*/
	proc transpose data=column_list out=flipped;
		by memname;
		id name;
		idlabel name;
		var count;
	run;
	
    /*Format output*/
	data want;
		retain memname;
		set flipped;
		array class(*) _NUMERIC_;
	
		do i=1 to dim(class);
	
			if class(i)=. then
				class(i)=0;
		end;
		All_Variables=sum(of _numeric_)-I;
		DROP I _NAME_;
	run;

I'll leave it up to you to generalize it.

Re: Identifying common column (variable) names from multiple tables

PaigeMiller — Tue, 08 Jun 2021 18:25:04 GMT

I would take the same test data sets created by @Reeza (Class1-Class5) and then run her PROC SQL, and then replace her PROC TRANSPOSE with this:

proc freq data=column_list;
    tables name/noprint out=Names;
run;

now you have a data set called NAMES where the number of times a variable appears is in the column named COUNT. From there, you can do whatever you need with this information. So if you want to know which names appear more than once (and how many times they appear) in these data sets, you just eliminate the variable names with COUNT=1.