Hi There,
I am having Macro var1 having multiple values seperated by delimitor (master list of charcter type variable), another macro var2 val seperated by dilimitor (another subgroup of Mixed type (Char+Num) ). i want to find out the common values between two macro variables. any one please help me.
Macro Var1 = Name sex race occp {all character type variables}
Macro Var2 = Name age sex salary race country {mixed type char and Num variables}
Solution Like Macro Var3 = Name sex race {only characters type vars
Thanks
So you have these macro variables.
%let charvars = Name sex race occp ;
%let allvars = Name age sex salary race country ;
And you want to produce.
%let found=Name sex race;
It would be easier to do it with a data step (if the values of the macro variables are not longer than 32K characters).
data _null_;
charvars="&charvars";
allvars="&allvars";
length found $32767 ;
do i=1 to countw(charvars,' ');
if indexw(upcase(allvars),upcase(scan(charvars,i,' '))) then
found=catx(' ',found,scan(charvars,i,' '))
;
end;
call symputx('found',found);
run;
Although you could do it inside of a macro. You could even define the macro to work like a function since it has no need to generate SAS code.
%macro findwords(allvars,charvars);
%local i next found ;
%do i=1 %to %sysfunc(countw(&charvars,%str( )));
%let next=%scan(&charvars,&i,%str( ));
%if %sysfunc(indexw(%upcase(&allvars),%upcase(&next),%str( ))) %then
%let found=&found &next
;
%end;
&found.
%mend findwords;
Then you could call it to create your list.
%let found=%findwords(&allvars,&charvars);
%put &=found ;
EDIT: this post can be ignored ... should not post without sufficient caffeine consumed.
With the names alone it is impossible to tell whether a variable is numeric or alphanumeric. You need the names of dataset and library and query sashelp.vcoulmn or another metadata-source.
You can select variables depending on there type from sashelp.column.
Untested code:
proc sql;
select *
from sashelp.vcolumn
where libname = 'SASHELP' and MemName = 'CLASS' and Type = 'char'
;
quit;
So you have these macro variables.
%let charvars = Name sex race occp ;
%let allvars = Name age sex salary race country ;
And you want to produce.
%let found=Name sex race;
It would be easier to do it with a data step (if the values of the macro variables are not longer than 32K characters).
data _null_;
charvars="&charvars";
allvars="&allvars";
length found $32767 ;
do i=1 to countw(charvars,' ');
if indexw(upcase(allvars),upcase(scan(charvars,i,' '))) then
found=catx(' ',found,scan(charvars,i,' '))
;
end;
call symputx('found',found);
run;
Although you could do it inside of a macro. You could even define the macro to work like a function since it has no need to generate SAS code.
%macro findwords(allvars,charvars);
%local i next found ;
%do i=1 %to %sysfunc(countw(&charvars,%str( )));
%let next=%scan(&charvars,&i,%str( ));
%if %sysfunc(indexw(%upcase(&allvars),%upcase(&next),%str( ))) %then
%let found=&found &next
;
%end;
&found.
%mend findwords;
Then you could call it to create your list.
%let found=%findwords(&allvars,&charvars);
%put &=found ;
Excellent advice. Also note, you can get rid of the local macro variable FOUND. Remove it from the %LOCAL statement, remove the final &FOUND. line, and make this change:
%if %sysfunc(indexw(%upcase(&allvars),%upcase(&next),%str( ))) %then &next;
Hi Tom
Thanks for your time to solve the issue, i appriciate your help.
Try this....
%let Var1 = Name sex race occp;
%let Var2 = Name age sex salary race country;
data
STRING1 (keep=string1 where=(string1 is not missing) )
STRING2 (keep=string2 where=(string2 is not missing) );
VAR1=upcase("&var1");
Var1wc=countw(var1);
var2=upcase("&var2");
var2wc=countw(var2);
do i=1 to max(var1wc,var2wc);
length string1 string2 $32;
string1 =scan(var1,i," ");
string2 =scan(var2,i," ");
output;
end;
run;
proc sql noprint;
select coalescec(string1 ,string2 ) into: var3 separated by " ";
from WANT A , WANT B
where string1= string2 ;
quit;
Hope this helps.
Hi ShiroAmanda,
Thank you for your time, Code was simple and easy to understand to every one. i appriciate your help.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.