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
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.
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;
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.
Many thanks. This is exactly what I am looking for. Thanks again for your great help!
Using the macro from your other thread.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.