count values from multiples tables, needing output in a single table with all counts combined

[dhrumilpatel02]

I am trying to get an output that has counts of multiple tables from different libraries. For Example:

Table descriptions:

Library name = a

table name = table_1

month	ID
January2021	123
January2021	456
February2021	123
February2021	456
February2021	789

Library name = b

table name = table_2

month	ID
January2021	456
January2021	789
January2021	741
February2021	111
February2021	654

I am able to get the counts for one table each in a macro as following:

%macro qa_counts(table_name=, month=, count_id=)

proc sql;

select &month, count(&count_id) as counts

from &table_name

group by &month

order by &month desc;

quit;

%mend qa_counts;

%qa_counts(table_name = a.table_1, month = month, count_id = ID)

%qa_counts(table_name = b.table_2, month = month, count_id = ID)

But I am looking for an output with the counts of both the tables in a single table output along with the table names, like this:

table_name	month	count_ID
a.table_1	January	2
a.table_1	February	3
b.table_2	January	3
b.table_2	February	2

Can someone please help?

[Oligolas]

Hi, 

There are many ways to achieve that.

You could either count the IDs for each table separately and put the results together afterwards or append the tables first and then count the IDs:

DATA ds0;
   length table_name $32;
   set table_1(in=t1) table_2(in=t2);
   if t1 then table_name='table_1';
   else if t2 then table_name='table_2';
RUN;

PROC SQL;
   CREATE TABLE want AS
      SELECT table_name, month, count(id) as count_ID
      FROM ds0
      GROUP BY table_name, month
      ORDER BY table_name, month DESC
   ;
   DROP TABLE ds0;
QUIT;

[s_lassen]

You could add the table name to the output:

%macro qa_counts(table_name=, month=, count_id=)
  proc sql;
     select "&table_name" as table_name, &month, count(&count_id) as counts
     from &table_name
    group by &month
    order by &month desc;
  quit;
%mend qa_counts;

This will give you the fields you want. If you want to collect it all in one dataset, you can do it like this:

%macro qa_counts(table_name=, month=, count_id=)
  proc sql;
     insert into ID_counts
     select "&table_name" as table_name, &month, count(&count_id) as counts
     from &table_name
    group by &month
    order by &month desc;
  quit;
%mend qa_counts;

So if you run this

data ID_counts;
  length table_name $20 month $10 ID 8;
  stop;
run;
%qa_counts(table_name = a.table_1, month = month, count_id = ID)
%qa_counts(table_name = b.table_2, month = month, count_id = ID)

you should have a table, ID_counts, with the data you want. You can then print that, if that is what you want.

[dhrumilpatel02]

@s_lassen This does look almost exactly how I wanted it, thanks a lot for that! Though I am getting an error while running your code at the moment. Here is the real data that contains in columns if it helps understand better with the lengths of each cell or anything else:

month	id
31JAN2021	623053599760700
28FEB2021	523222256455200
31MAR2021	315223178730000
30APR2021	323270691739500

Here is the error text from the log:

WARNING: Character expression will be truncated when assigned to character column table_name.
ERROR: Value 2 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

Can you please help solving the error? Let me know if you need any other information!

[Ksharp · Accepted solution]

Here could give you a start.

%macro qa_counts(table_name = , month = , count_id = );
proc sql;
create table temp as
select "&table_name." as table_name length=40, 
 put(&month.,monname.) as month length=40,
 count(distinct &count_id.) as count_id
 from &table_name.
  group by month ;
quit;
proc append base=want data=temp force;run;
%mend;


proc delete data=want;run;
%qa_counts(table_name = sashelp.stocks, month = date, count_id = stock)


proc print data=want noobs;run;