Formating SQL Queries in a single clean table

[tish]

%MACRO count_duplicates;

   %do i = 1 %to &table_number;

      %let table_to_use = %scan(&table_list, &i);

      %let variable_to_use = %scan(&name_variable, &i);

      %if &i = 1 %then

         %do;

            create table summary as

         %end;

      %else

         %do;

            insert into summary

         %end;

      SELECT

         "&table_to_use" as table_name,

         %if   &variable_to_use = name %then

            %do;

               name as combined_name,

            %end;

         %else if

               &variable_to_use = last_name %then

            %do;

               last_name as combined_name,

            %end;

         %else if

               &variable_to_use = first_name %then

            %do;

               first_name as combined_name,

            %end;

         COUNT(name) AS NumOfccurrences

      FROM

         work.&table_to_use

      GROUP BY

         calculated combined_name

      HAVING

         COUNT(calculated combined_name) > 1;

   %end;

%MEND;

%let table_list = aaa bbb ccc ddd;

%let name_variable = name name name last_name;

%let table_number = %eval(%sysfunc(count(&table_list, %str( )))+1);

proc sql;

   %count_duplicates;

   select *

   from summary

   order by table_name;

quit;

Again, I can't test this. It might blow up on the use of the calculated variable in the group by and having clauses. In that case, I'd use another %if-%then-%else block of macro code to substitute the real variable names for sql to use. (And then the code will really look monstrous!)

[Howles]

To answer the original question: Replace each statement boundary (semicolon) with a UNION ALL operator.

If there will be more tasks with these data in the future, bind the separate tables into one. One approach is to put all of the data in one big table, then discard or archive the 15 small tables. Or, keep the original tables and define a view to reference them as one. UNION operator can help with either,

nicnad wrote:

Hi,

I am using SAS enterprise guide 4.1 which is running SAS 9.1.

I have the following SQL queries, which counts the number of duplicates for a specific variables within a specific table :

proc sql;
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test1 GROUP BY name HAVING ( COUNT(name) > 1 );
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test2 GROUP BY name HAVING ( COUNT(name) > 1 );
SELECT last_name, COUNT(last_name) AS NumOfccurrences FROM work.test3 GROUP BY last_name HAVING ( COUNT(last_name) > 1 );
SELECT first_name, COUNT(first_name) AS NumOfccurrences FROM work.test4 GROUP BY first_name HAVING ( COUNT(first_name) > 1 );
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test5 GROUP BY name HAVING ( COUNT(name) > 1 );
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test6 GROUP BY name HAVING ( COUNT(name) > 1 );
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test7 GROUP BY name HAVING ( COUNT(name) > 1 );
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test8 GROUP BY name HAVING ( COUNT(name) > 1 );
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test9 GROUP BY name HAVING ( COUNT(name) > 1 );
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test10 GROUP BY name HAVING ( COUNT(name) > 1 );
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test11 GROUP BY name HAVING ( COUNT(name) > 1 );
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test12 GROUP BY name HAVING ( COUNT(name) > 1 );
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test13 GROUP BY name HAVING ( COUNT(name) > 1 );
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test14 GROUP BY name HAVING ( COUNT(name) > 1 );
SELECT name, COUNT(name) AS NumOfccurrences FROM work.test15 GROUP BY name HAVING ( COUNT(name) > 1 )
;quit;

Instead of having an HTML output with 15 different tables, is there an easy way to have an output (html or sas table) where all  those queries would be grouped by table name and variable?

Output should look like table below,

Thank you for your help and time!




work.test1
name
last_name
first_name


work.test2





work.test3





work.test4





work.test5





work.test6





work.test7





work.test8





work.test9





work.test10





work.test11





work.test12





work.test13





work.test14





work.test15