topic Sum of Count in New SAS User

Sum of Count

SAS_New_User1 — Wed, 27 Jan 2021 22:14:36 GMT

I want to do a sum of count of 3 tables.

Table 1

ColumnA

Table 2

ColumnB

Table 3

ColumnC

I want to do a sum of the count of the 3 tables i.e.

Sum (count(ColumnA) + count(columnB) + count(columnC)) as "Final Sum"

Sum (4 +3 +5)

My final output should be

Final Sum

Re: Sum of Count

mklangley — Wed, 27 Jan 2021 22:43:13 GMT

How do you want your final output--as a dataset, or a printed value?

Here's one way to do it--counting the total number of rows from those three datasets. This code will save the final sum to a macro variable, and print it.

data table1;
    input columnA;
    datalines;
    12
    10
    5
    6
    ;
run;

data table2;
    input columnB;
    datalines;
    8
    13
    34
    ;
run;

data table3;
    input columnC;
    datalines;
    20
    2
    24
    1
    17
    ;
run;

/* Combine table1, table2 and table3 */
data have;
    set table1 table2 table3;
run;

/* Put the total Final Sum in a macro variable */
proc sql;
    select count(*) into :final_sum
    from have;
quit;

%put The Final Sum is: &final_sum.;

Re: Sum of Count

Reeza — Wed, 27 Jan 2021 23:07:45 GMT

Will it always be three tables?
Do the table names have anything in common?

You can use the table dictionary.table or sashelp.vtable that contain the # of rows of each table and sum the number from that table.

proc means data=sashelp.vtable (where=(memname = 'WORK' and libname like 'TABLE%')) sum;
var nobs;
output out=want SUM=TOTAL_OBS;
run;

This pipes the output to a data set called WANT.

Re: Sum of Count

Kurt_Bremser — Thu, 28 Jan 2021 09:21:56 GMT

proc sql;
select sum(nobs) as final_count
from dictionary.tables
where libname = "WORK" and memname in ("TABLE1","TABLE2","TABLE3");
quit;

Use an appropriate WHERE clause.