Hi,
I would like to check if data set qt's variable names belong to variables names of another data set rit. The below is how I did it, but when I use 'name in &ritnames' in following code,
I need those ritnames in quotation marks, but they are not. How to add these quotation marks to the variables in &ritnames to make it work? Or someone can suggest another way
to achieve the same purpose without using my way? Thanks.
%let rit = gw.rit;
%let qt = gw.qt;
proc sql noprint;
select name into :ritnames separated by "," from dictionary.columns
where libname="%upcase(%scan(&rit,1))" and memname="%upcase(%scan(&rit,2))";
select name into :qtnames separated by "," from dictionary.columns
where libname="%upcase(%scan(&qt,1))" and memname="%upcase(%scan(&qt,2))"
and name in (&ritnames);
quit;
%let rit = gw.rit;
%let qt = gw.qt;
proc sql noprint;
select quote(cats(name)) into :ritnames separated by "," from dictionary.columns
where libname="%upcase(%scan(&rit,1))" and memname="%upcase(%scan(&rit,2))";
select name into :qtnames separated by "," from dictionary.columns
where libname="%upcase(%scan(&qt,1))" and memname="%upcase(%scan(&qt,2))"
and name in (&ritnames);
quit;
Or something like:
proc sql noprint;
select name into :qtnames separated by "," from dictionary.columns
where libname=upcase(scan("&qt",1)) and memname=upcase(scan("&qt",2))
and name in
(select name from dictionary.columns
where libname=upcase(scan("&rit",1)) and memname=upcase(scan("&rit",2)));
quit;
So that you don't have to worry about the 64K length limit of the first macro variable.
%let rit = gw.rit;
%let qt = gw.qt;
proc sql noprint;
select quote(cats(name)) into :ritnames separated by "," from dictionary.columns
where libname="%upcase(%scan(&rit,1))" and memname="%upcase(%scan(&rit,2))";
select name into :qtnames separated by "," from dictionary.columns
where libname="%upcase(%scan(&qt,1))" and memname="%upcase(%scan(&qt,2))"
and name in (&ritnames);
quit;
Or something like:
proc sql noprint;
select name into :qtnames separated by "," from dictionary.columns
where libname=upcase(scan("&qt",1)) and memname=upcase(scan("&qt",2))
and name in
(select name from dictionary.columns
where libname=upcase(scan("&rit",1)) and memname=upcase(scan("&rit",2)));
quit;
So that you don't have to worry about the 64K length limit of the first macro variable.
Very nice answers. The first version uses quoted variable name list while the second version uses a SAS variable column.
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 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.