DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

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

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;


Accepted Solutions
Solution
‎09-07-2013 08:01 PM
Respected Advisor
Posts: 3,156

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

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

View solution in original post


All Replies
Solution
‎09-07-2013 08:01 PM
Respected Advisor
Posts: 3,156

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

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

Contributor
Posts: 50

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 208 views
  • 0 likes
  • 2 in conversation