topic Re: how to create the list of variables common to both var-list-1 and var-list-2. in SAS Procedures

how to create the list of variables common to both var-list-1 and var-list-2.

Sunny_Sun — Tue, 10 Mar 2015 19:58:15 GMT

Hi,

I have to create a new variable list that is common to to the other two variables.

%LET large=a b c d e f g h i j k;

%LET exclude=c d h k m n;

I want to get a new variable, that only keeps the common variable list between large and exclude, that is, %let common=c d h k.

Thanks,

Sunny

Re: how to create the list of variables common to both var-list-1 and var-list-2.

slchen — Tue, 10 Mar 2015 20:15:56 GMT

This is what you need?

proc sql;

select name into:common separated by ' ' from dictionary.columns where libname='WORK' and memname='LARGE' and upcase(name) in (select upcase(name) from dictionary.columns where libname='WORK' and memname='EXCLUDE');

quit;

Re: how to create the list of variables common to both var-list-1 and var-list-2.

Astounding — Tue, 10 Mar 2015 20:24:25 GMT

Here's a macro that returns the list of common fields. You may have to add %UPCASE here and there, if you want "a" and "A" to represent a match:

%macro common (list1, list2);

%local i nextname common;

%do i=1 %to %sysfunc(countw(&list1));

%let nextname = %scan(&list1, &i);

%if %index(%str( &list2 ), %str( &nextname )) %then %let common = &common &nextname;

%end;

&common

%mend common;

You would use the macro reference wherever you wanted to see the list of common variable names. For example:

%let common = %common(&list1, &list2);

Or:

array comvars {*} %common(&list1, &list2);

The macro's output is the text consisting of the common names.

Good luck.

Re: how to create the list of variables common to both var-list-1 and var-list-2.

Sunny_Sun — Tue, 10 Mar 2015 20:43:17 GMT

Many thanks. This is exactly what I am looking for. Thanks again for your great help!

Re: how to create the list of variables common to both var-list-1 and var-list-2.

data_null__ — Tue, 10 Mar 2015 20:45:59 GMT

Using the macro from your other thread.

data class;
   set sashelp.class(obs=2);
   retain a b c d e 'x' f g h i j k m n 1; 
   run; 
page; 

%LET list1=a b c d e f g h i j k;
%LET list2=c d h k m n;

%let subset=%expand_varlist(data=class,keep=&list2,outexpr=quote(strip(_name_)));
%let common=%expand_varlist(data=class,keep=&list1,where=_name_ in(&subset));
%put NOTE: "&=List1" with "&=list2" common variables is "&common";

Log:

104        %LET list1=a b c d e f g h i j k;
105        %LET list2=c d h k m n;
106        
107        %let subset=%expand_varlist(data=class,keep=&list2,outexpr=quote(strip(_name_)));
NOTE: Macro(EXPAND_VARLIST) retured: """c"" ""d"" ""h"" ""k"" ""m"" ""n"""
108        %let common=%expand_varlist(data=class,keep=&list1,where=_name_ in(&subset));
NOTE: Macro(EXPAND_VARLIST) retured: "c d h k"
109        %put NOTE: "&=List1" with "&=list2" common variables is "&common"; 
NOTE: "LIST1=a b c d e f g h i j k" with "LIST2=c d h k m n" common variables is "c d h k"

Re: how to create the list of variables common to both var-list-1 and var-list-2.

Haikuo — Tue, 10 Mar 2015 20:55:40 GMT

I can't test it, but it seems to me if your scenario came as is, a small tweak to the solution of your precious question should do,

%LET large=a b c d e f g h i j k;

%LET exclude=c d h k m n;

%let final=%sysfunc(compress(&large,&exclude.,k));

%put final=&final;

Re: how to create the list of variables common to both var-list-1 and var-list-2.

BOBSAS — Wed, 11 Mar 2015 18:50:19 GMT

What if the column names are like this. instead of a b c its ab cd name etc. Compress will remove all a and b letters.

%LET large=ab bc name;

%LET exclude=ab;

%let final=%sysfunc(compress(&large,&exclude.,k));

%put final=&final;

Re: how to create the list of variables common to both var-list-1 and var-list-2.

Haikuo — Thu, 12 Mar 2015 02:11:44 GMT

Exactly. My solution has its context, in which , this caveat has been exposed to the same OP. But obviously the context has been buried too deep, so thanks for pointing it out.