topic Re: How to check if variable names of one data set are variable names of another data set ? in SAS Programming

How to check if variable names of one data set are variable names of another data set ?

Macro — Sat, 07 Sep 2013 21:28:55 GMT

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;

Re: How to check if variable names of one data set are variable names of another data set ?

Haikuo — Sun, 08 Sep 2013 00:01:50 GMT

%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.

Re: How to check if variable names of one data set are variable names of another data set ?

Macro — Sun, 08 Sep 2013 06:03:37 GMT

Very nice answers. The first version uses quoted variable name list while the second version uses a SAS variable column.