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
... View more
