topic Re: Create report counting all past months tables in SAS Programming

Create report counting all past months tables

Son_Of_Krypton — Wed, 04 Nov 2020 10:54:05 GMT

Hello,

I want to Create a reasonability report counting all past months tables – count distinct on KEY.
Include a trending report that shows the total distinct KEY for each historic month.

Can anybody help me on this.

Re: Create report counting all past months tables

PeterClemmensen — Wed, 04 Nov 2020 11:08:03 GMT

Can you show us your data and your desired result?

Re: Create report counting all past months tables

Son_Of_Krypton — Wed, 04 Nov 2020 15:22:21 GMT

I have 5 tables in library Temp

Table Name with obs counts

Table1-35678

Table2-54987

Table3-68775

Table4-34000

Table5-15000

I want result like this in another table

Table List	Table Rows Count	Distinct Key Count
Table1	35678	27890
Table2	54987	50000
Table3	68775	34980
Table4	34000	24578
Table5	15000	9845

Re: Create report counting all past months tables

ballardw — Wed, 04 Nov 2020 15:46:39 GMT

@Son_Of_Krypton wrote:

I have 5 tables in library Temp

Table Name with obs counts

Table1-35678

Table2-54987

Table3-68775

Table4-34000

Table5-15000

I want result like this in another table

Table List Table Rows Count Distinct Key Count

Table1 35678 27890

Table2 54987 50000

Table3 68775 34980

Table4 34000 24578

Table5 15000 9845

How do we know which of these are " past months table"?

Re: Create report counting all past months tables

Son_Of_Krypton — Wed, 04 Nov 2020 16:12:32 GMT

I have 5 tables in library Temp

Table Name with obs counts

Table1-35678

Table2-54987

Table3-68775

Table4-34000

Table5-15000

I want result like this in another table

Table List	Table Rows Count	Distinct Key Count
Table_2020_Jul_04	35678	27890
Table2_2020_Aug_05	54987	50000
Table3_2020_Sep_06	68775	34980
Table4_2020_Oct_03	34000	24578
Table5_2020_Nov_03	15000	9845

Re: Create report counting all past months tables

ballardw — Wed, 04 Nov 2020 16:45:00 GMT

What defines "distinct Key"? Is that based on one variable, two, 27? If so which variable(s). Are they the same in each of these data sets?

Still don't see a rule defining " all past months tables". Are this all in the same SAS library? Different libraries?

Re: Create report counting all past months tables

Kurt_Bremser — Wed, 04 Nov 2020 16:50:59 GMT

data all / view =all;
length dsname dname $41;
set temp.table: indsname=dname;
dsname = dname;
run;

proc sql;
create table want as
  select
    dsname,
    count(*) as rowcount,
    count(distinct key) as d_key_count
  from all
  group by dsname
;
quit;

Re: Create report counting all past months tables

Son_Of_Krypton — Thu, 05 Nov 2020 06:39:21 GMT

ghetting this error

616 set temp.table: indsname=dname;
^
ERROR: Found "indsname" when expecting one of END, KEY, NOBS or POINT

i have 5 tables in temp library

Re: Create report counting all past months tables

Kurt_Bremser — Thu, 05 Nov 2020 07:16:27 GMT

Please post the complete log of the step (code and messages). Use the </> button for posting logs (required!).

Re: Create report counting all past months tables

Son_Of_Krypton — Thu, 05 Nov 2020 07:19:31 GMT

654       data all / view =all;
655       length dsname dname $41;
656       set work.table: indsname=dname;
                          ^
ERROR: Found "indsname" when expecting one of END, KEY, NOBS or POINT
657       dsname = dname;
658       run;

Re: Create report counting all past months tables

Kurt_Bremser — Thu, 05 Nov 2020 10:03:03 GMT

I just ran this:

data table1;
set sashelp.class;
run;

data table2;
set sashelp.class;
run;

data all / view=all;
length dsname dname $41;
set work.table: indsname=dname;
dsname = dname;
run;

proc sql;
create table want as
  select
    dsname,
    count(*) as count,
    count(distinct sex) as dcount
  from all
  group by dsname
;
quit;

proc print data=want noobs;
run;

on my University Edition with no problems and the expected result:

dsname	count	dcount
WORK.TABLE1	19	2
WORK.TABLE2	19	2

Please run this:

%put &sysvlong;

and post the log.

Re: Create report counting all past months tables

Son_Of_Krypton — Thu, 05 Nov 2020 10:12:34 GMT

what if my table names are different

Re: Create report counting all past months tables

Kurt_Bremser — Thu, 05 Nov 2020 10:27:47 GMT

@Son_Of_Krypton wrote:

what if my table names are different

Then use them in the SET of the view; I just illustrated that the wildcard and the INDSNAME work with a current SAS version.

Re: Create report counting all past months tables

Son_Of_Krypton — Thu, 05 Nov 2020 13:12:05 GMT

is there any other way we can do this.??

i mean by using dictionary.tables if yes the how.??

Re: Create report counting all past months tables

Kurt_Bremser — Thu, 05 Nov 2020 13:49:18 GMT

@Son_Of_Krypton wrote:

is there any other way we can do this.??

i mean by using dictionary.tables if yes the how.??

The dictionary tables can only give you the total number of observations in a dataset, so they don't meet your needs.

Re: Create report counting all past months tables

Son_Of_Krypton — Mon, 09 Nov 2020 11:24:07 GMT

Yes tables are in same library but with different names and want result as below and it has the common key

Table List Table Rows Count Distinct Key Count
Test_2020_Jul_04 35678 27890
Temp_2020_Aug_05 54987 50000
Check_2020_Sep_06 68775 34980
Members_2020_Oct_03 34000 24578
Table5_2020_Nov_03 15000 9845