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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.