Building master table with input from multiple tables

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Building master table with input from multiple tables

[ Edited ]

 

I have some 10 variables, on which I have to perform 10 different data quality (Null, zero value, length, validity of date..) checks. And I am generating one table for each  1Var * 1 DQ check, resulting in 100 tables. Each table will hold just a control total, i.e. count of distinct value for a variable.

Proc sql;
create table DQ_Check_Zero_Var1 as
select count(distinct Var1) as Count_Zero from Source_table;
quit;


Now, I would like to create a master table in this format. Each cell will hold the only value from the corresponding table.


     Var1                                      Var2                             Var3                               Var4                                   Var5              ...
Count_Zero_V1                Count_Zero_V2              Count_Zero_V3         
Count_Neg_V1                 Count_Neg_V2               Count_Neg_V3
Count_Length_V1             Count_Length_V2          Count_Length_V3
Count_ValidDate_V1         Count_ValidDate_V2      Count_ValidDate_V3
.
.
.
.

 

I need some idea on how to generate this code.

 

Thank you.


Accepted Solutions
Solution
‎10-12-2015 08:53 PM
Respected Advisor
Posts: 4,609

Re: Building master table with input from multiple tables

I assumed that your datasets have names like DQ_Check_Stat_Var where Stat is the name of the statistic and Var is the name of the variable

 

Proc sql;
create table DQ_Check_N_height as 
select count(height) as Count_Not_Null from SasHelp.class;
quit;

Proc sql;
create table DQ_Check_N_weight as 
select count(weight) as Count_Not_Null from SasHelp.class;
quit;

Proc sql;
create table DQ_Check_Range_height as 
select range(height) as Range from SasHelp.class;
quit;

Proc sql;
create table DQ_Check_Range_weight as 
select range(weight) as Range from SasHelp.class;
quit;

data all;
set DQ_Check_: INDSNAME=ds;
length var Stat $32;
var = propcase(scan(ds, -1, "_"));
stat = propcase(scan(ds, -2, "_"));
value = coalesce(of _numeric_);
keep var stat value;
run;

proc sort data=all; by stat var; run;

proc transpose data=all out=want( drop=_: );
by stat;
id var;
var value;
run;

proc print data=want noobs; run;
PG

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,199

Re: Building master table with input from multiple tables

I would make a generic table, holding columns Table_name (so this can be used new similar audits), Variable_name, Variable_value and Variable_count. Perhaps a timestamp and some source data pointer could be nice for traceability.
Data never sleeps
Solution
‎10-12-2015 08:53 PM
Respected Advisor
Posts: 4,609

Re: Building master table with input from multiple tables

I assumed that your datasets have names like DQ_Check_Stat_Var where Stat is the name of the statistic and Var is the name of the variable

 

Proc sql;
create table DQ_Check_N_height as 
select count(height) as Count_Not_Null from SasHelp.class;
quit;

Proc sql;
create table DQ_Check_N_weight as 
select count(weight) as Count_Not_Null from SasHelp.class;
quit;

Proc sql;
create table DQ_Check_Range_height as 
select range(height) as Range from SasHelp.class;
quit;

Proc sql;
create table DQ_Check_Range_weight as 
select range(weight) as Range from SasHelp.class;
quit;

data all;
set DQ_Check_: INDSNAME=ds;
length var Stat $32;
var = propcase(scan(ds, -1, "_"));
stat = propcase(scan(ds, -2, "_"));
value = coalesce(of _numeric_);
keep var stat value;
run;

proc sort data=all; by stat var; run;

proc transpose data=all out=want( drop=_: );
by stat;
id var;
var value;
run;

proc print data=want noobs; run;
PG
Contributor
Posts: 36

Re: Building master table with input from multiple tables

Thank you, PGStats. This is what I was trying to achieve.

 

I will modify the code as per my need.

 

Thank you again.

 

 

Respected Advisor
Posts: 3,840

Re: Building master table with input from multiple tables

@zoomzoom

If you're happy with the answer then please mark it as solution so we can see in the overview that this thread is "closed"

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 308 views
  • 1 like
  • 4 in conversation