BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dhrumilpatel02
Calcite | Level 5

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

 

monthID
January2021123
January2021456
February2021123
February2021456
February2021

789

 

Library name = b

table name = table_2

 

monthID
January2021456
January2021789
January2021741
February2021111
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_namemonthcount_ID
a.table_1January2
a.table_1February3
b.table_2January3
b.table_2February2

 

Can someone please help?

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

4 REPLIES 4
Oligolas
Barite | Level 11

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;
________________________

- Cheers -

s_lassen
Meteorite | Level 14

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
Calcite | Level 5

@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:

monthid
31JAN2021623053599760700
28FEB2021523222256455200
31MAR2021315223178730000
30APR2021323270691739500

 

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
Super User

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;

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2403 views
  • 3 likes
  • 4 in conversation