BookmarkSubscribeRSS Feed
Son_Of_Krypton
Fluorite | Level 6

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.

15 REPLIES 15
PeterClemmensen
Tourmaline | Level 20

Can you show us your data and your desired result?

Son_Of_Krypton
Fluorite | Level 6

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 ListTable Rows CountDistinct Key Count
Table13567827890
Table25498750000
Table36877534980
Table43400024578
Table5150009845

 

 

ballardw
Super User

@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"?

 

 

Son_Of_Krypton
Fluorite | Level 6

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 ListTable Rows CountDistinct Key Count
Table_2020_Jul_043567827890
Table2_2020_Aug_055498750000
Table3_2020_Sep_066877534980
Table4_2020_Oct_033400024578
Table5_2020_Nov_03150009845

 

ballardw
Super User

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?

 

Son_Of_Krypton
Fluorite | Level 6
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
Kurt_Bremser
Super User
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;
Son_Of_Krypton
Fluorite | Level 6
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
Son_Of_Krypton
Fluorite | Level 6
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;
Kurt_Bremser
Super User

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.

Son_Of_Krypton
Fluorite | Level 6

what if my table names are different

Kurt_Bremser
Super User

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

Son_Of_Krypton
Fluorite | Level 6

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

 

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

 

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 15 replies
  • 2424 views
  • 0 likes
  • 4 in conversation