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.
Can you show us your data and your desired result?
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 |
@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"?
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 |
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?
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;
Please post the complete log of the step (code and messages). Use the </> button for posting logs (required!).
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;
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.
what if my table names are different
@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.
is there any other way we can do this.??
i mean by using dictionary.tables if yes the how.??
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.
Ready to level-up your skills? Choose your own adventure.