BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sunny_Sun
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

7 REPLIES 7
slchen
Lapis Lazuli | Level 10

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;

Astounding
PROC Star

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.

Sunny_Sun
Calcite | Level 5

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

data_null__
Jade | Level 19

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"
Haikuo
Onyx | Level 15

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;

BOBSAS
Calcite | Level 5

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;

Haikuo
Onyx | Level 15

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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