Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

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

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


Accepted Solutions
Solution
‎03-10-2015 04:24 PM
Super User
Posts: 5,085

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

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.

View solution in original post


All Replies
Super Contributor
Posts: 275

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

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;

Solution
‎03-10-2015 04:24 PM
Super User
Posts: 5,085

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

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.

Contributor
Posts: 33

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

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

Respected Advisor
Posts: 3,777

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

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"
Respected Advisor
Posts: 3,124

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

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;

Contributor
Posts: 24

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

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;

Respected Advisor
Posts: 3,124

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

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 327 views
  • 3 likes
  • 6 in conversation