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?
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;
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 -
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.
@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!
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: