DATA CLEASING

[Ronein]

Hello

I have a data set with 2 million rows and 150 columns.

Each customer ID have one row.

The columns are from 3 types of fields-

continuous (such as wealth amount),

count (such as number of active loans),

binary (such as indicator of failure in payments)

CHAR( such as address)

I want to check the data  if it has no problems (for example : if a specific field has only null values )

I thoughts about create 3 reports for each type of variable-

For each continuous/count var check- 

number of customers with null value

number of customers with  value 0

number of customers with POS value

For each binary var check- 
number of customers with null value
number of customers with  value 0
number of customers with value 1

For each char var check- 
number of customers with null value
number of customers with not null value

What is the way (code) to create it please?

options missing=.;
Data have;
input CustID  x1 x2 x3 w1 w2  B1 B2 C1 $ C2 $ ;
cards;
1 10.1 15 20 0 2 0 0 abx .
2 . . 21.1 0 0 1 1 abx def 
3 12 14.4 16 . . 1 1  abx def
4 18 . 20.8 0 0 0 1 abx def
5 . . .  1 3 0 0 . def
;
run;

[Ksharp · Accepted solution]

CODE UPDATED .

options missing=.;
Data have;
input CustID  x1 x2 x3 w1 w2  B1 B2 C1 $ C2 $ ;
cards;
1 10.1 15 20 0 2 0 0 abx .
2 . . 21.1 0 0 1 1 abx def 
3 12 14.4 16 . . 1 1  abx def
4 18 . 20.8 0 0 0 1 abx def
5 . . .  1 3 0 0 . def
;
run;
title;
proc sql;
select 'x1' as field,'continuous' as type,nmiss(x1) as nr_null,sum(x1=0) as nr_0,sum(x1>0) as nr_pos  from have
union all
select 'x2' as field,'continuous' as type,nmiss(x2) as nr_null,sum(x2=0) as nr_0,sum(x2>0) as nr_pos  from have
union all
select 'x3' as field,'continuous' as type,nmiss(x3) as nr_null,sum(x3=0) as nr_0,sum(x3>0) as nr_pos  from have
union all
select 'w1' as field,'count' as type,nmiss(w1) as nr_null,sum(w1=0) as nr_0,sum(w1>0) as nr_pos  from have
union all
select 'w2' as field,'count' as type,nmiss(w2) as nr_null,sum(w2=0) as nr_0,sum(w2>0) as nr_pos  from have
;


select 'b1' as field,'binary' as type,nmiss(b1) as nr_null,sum(b1=0) as nr_0,sum(b1=1) as nr_1  from have
union all
select 'b2' as field,'binary' as type,nmiss(b2) as nr_null,sum(b2=0) as nr_0,sum(b2=1) as nr_1  from have
;


select 'c1' as field,'char' as type,nmiss(c1) as nr_null,n(c1) as nr_not_null  from have
union all
select 'c2' as field,'char' as type,nmiss(c2) as nr_null,n(c2) as nr_not_null  from have
;

quit;

[Ronein]

Thank you

it is perfect, 

However, I want to ask please-

In real world I have 150 columns and my question is how can I make a clever code that perform it for all of the columns?

Should I write 100 rows :(for each continuous var)?

select 'x1' as field,'continuous' as type,nmiss(x1) as nr_null,sum(x1=0) as nr_0,sum(x1>0) as nr_pos  from have

[Tom]

How do you know which variables are binary and which are continuous?

Presumably you have a DATASET with the list of variables and their type/category.   So use that to generate the code.

[Ronein]

My question was IF there is a more clever and short code to run this:

Here I have for each var a separate code.

since it is proc sql then I dont know to use array here /

Any idea how to write it in a better way?

proc sql;
create table t_contunous_CONUT_Vars as
SELECT     ' Case_Id '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Case_Id)  AS  SUM,nmiss( Case_Id)  AS  Nr_Null,SUM(Case_Id=0)  AS  Nr_0,SUM(Case_Id>0)  AS  Nr_POS,SUM(Case_Id<0   AND  Case_Id  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Avg_Payoff '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Avg_Payoff_Last_Month)  AS  SUM,nmiss( Avg_Payoff_Last_Month)  AS  Nr_Null,SUM(Avg_Payoff_Last_Month=0)  AS  Nr_0,SUM(Avg_Payoff_Last_Month>0)  AS  Nr_POS,SUM(Avg_Payoff_Last_Month<0   AND  Avg_Payoff_Last_Month  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Mortage '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Mortage_Last_Month)  AS  SUM,nmiss( Sum_Mortage_Last_Month)  AS  Nr_Null,SUM(Sum_Mortage_Last_Month=0)  AS  Nr_0,SUM(Sum_Mortage_Last_Month>0)  AS  Nr_POS,SUM(Sum_Mortage_Last_Month<0   AND  Sum_Mortage_Last_Month  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Mortage'  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Mortage_Last_Month_)  AS  SUM,nmiss( Sum_Mortage_Last_Month_)  AS  Nr_Null,SUM(Sum_Mortage_Last_Month_=0)  AS  Nr_0,SUM(Sum_Mortage_Last_Month_>0)  AS  Nr_POS,SUM(Sum_Mortage_Last_Month_<0   AND  Sum_Mortage_Last_Month_  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Checs '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Checs)  AS  SUM,nmiss( Sum_Checs)  AS  Nr_Null,SUM(Sum_Checs=0)  AS  Nr_0,SUM(Sum_Checs>0)  AS  Nr_POS,SUM(Sum_Checs<0   AND  Sum_Checs  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Cash '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Cash)  AS  SUM,nmiss( Sum_Cash)  AS  Nr_Null,SUM(Sum_Cash=0)  AS  Nr_0,SUM(Sum_Cash>0)  AS  Nr_POS,SUM(Sum_Cash<0   AND  Sum_Cash  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Transfer '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Transfer)  AS  SUM,nmiss( Sum_Transfer)  AS  Nr_Null,SUM(Sum_Transfer=0)  AS  Nr_0,SUM(Sum_Transfer>0)  AS  Nr_POS,SUM(Sum_Transfer<0   AND  Sum_Transfer  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Transfer_Same_Inv '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Transfer_Same_Inv)  AS  SUM,nmiss( Sum_Transfer_Same_Inv)  AS  Nr_Null,SUM(Sum_Transfer_Same_Inv=0)  AS  Nr_0,SUM(Sum_Transfer_Same_Inv>0)  AS  Nr_POS,SUM(Sum_Transfer_Same_Inv<0   AND  Sum_Transfer_Same_Inv  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_G_Credit '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_G_Credit)  AS  SUM,nmiss( Sum_G_Credit)  AS  Nr_Null,SUM(Sum_G_Credit=0)  AS  Nr_0,SUM(Sum_G_Credit>0)  AS  Nr_POS,SUM(Sum_G_Credit<0   AND  Sum_G_Credit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Checs_Debit '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Checs_Debit)  AS  SUM,nmiss( Sum_Checs_Debit)  AS  Nr_Null,SUM(Sum_Checs_Debit=0)  AS  Nr_0,SUM(Sum_Checs_Debit>0)  AS  Nr_POS,SUM(Sum_Checs_Debit<0   AND  Sum_Checs_Debit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Cash_Debit '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Cash_Debit)  AS  SUM,nmiss( Sum_Cash_Debit)  AS  Nr_Null,SUM(Sum_Cash_Debit=0)  AS  Nr_0,SUM(Sum_Cash_Debit>0)  AS  Nr_POS,SUM(Sum_Cash_Debit<0   AND  Sum_Cash_Debit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Transfer_Debit '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Transfer_Debit)  AS  SUM,nmiss( Sum_Transfer_Debit)  AS  Nr_Null,SUM(Sum_Transfer_Debit=0)  AS  Nr_0,SUM(Sum_Transfer_Debit>0)  AS  Nr_POS,SUM(Sum_Transfer_Debit<0   AND  Sum_Transfer_Debit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Transfer_Debit_Same_I '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Transfer_Debit_Same_I)  AS  SUM,nmiss( Sum_Transfer_Debit_Same_I)  AS  Nr_Null,SUM(Sum_Transfer_Debit_Same_I=0)  AS  Nr_0,SUM(Sum_Transfer_Debit_Same_I>0)  AS  Nr_POS,SUM(Sum_Transfer_Debit_Same_I<0   AND  Sum_Transfer_Debit_Same_I  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Loans_Last_Month_Nashaf '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Loans_Last_Month_Nashaf)  AS  SUM,nmiss( Sum_Loans_Last_Month_Nashaf)  AS  Nr_Null,SUM(Sum_Loans_Last_Month_Nashaf=0)  AS  Nr_0,SUM(Sum_Loans_Last_Month_Nashaf>0)  AS  Nr_POS,SUM(Sum_Loans_Last_Month_Nashaf<0   AND  Sum_Loans_Last_Month_Nashaf  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Cc_M_1 '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Cc_M_1)  AS  SUM,nmiss( Sum_Cc_M_1)  AS  Nr_Null,SUM(Sum_Cc_M_1=0)  AS  Nr_0,SUM(Sum_Cc_M_1>0)  AS  Nr_POS,SUM(Sum_Cc_M_1<0   AND  Sum_Cc_M_1  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Cc_M_2 '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Cc_M_2)  AS  SUM,nmiss( Sum_Cc_M_2)  AS  Nr_Null,SUM(Sum_Cc_M_2=0)  AS  Nr_0,SUM(Sum_Cc_M_2>0)  AS  Nr_POS,SUM(Sum_Cc_M_2<0   AND  Sum_Cc_M_2  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Cc_M_3 '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Cc_M_3)  AS  SUM,nmiss( Sum_Cc_M_3)  AS  Nr_Null,SUM(Sum_Cc_M_3=0)  AS  Nr_0,SUM(Sum_Cc_M_3>0)  AS  Nr_POS,SUM(Sum_Cc_M_3<0   AND  Sum_Cc_M_3  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Cc_M_4 '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Cc_M_4)  AS  SUM,nmiss( Sum_Cc_M_4)  AS  Nr_Null,SUM(Sum_Cc_M_4=0)  AS  Nr_0,SUM(Sum_Cc_M_4>0)  AS  Nr_POS,SUM(Sum_Cc_M_4<0   AND  Sum_Cc_M_4  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Cc_M_5 '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Cc_M_5)  AS  SUM,nmiss( Sum_Cc_M_5)  AS  Nr_Null,SUM(Sum_Cc_M_5=0)  AS  Nr_0,SUM(Sum_Cc_M_5>0)  AS  Nr_POS,SUM(Sum_Cc_M_5<0   AND  Sum_Cc_M_5  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Loan_Exbank_Amt '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Loan_Exbank_Amt)  AS  SUM,nmiss( Loan_Exbank_Amt)  AS  Nr_Null,SUM(Loan_Exbank_Amt=0)  AS  Nr_0,SUM(Loan_Exbank_Amt>0)  AS  Nr_POS,SUM(Loan_Exbank_Amt<0   AND  Loan_Exbank_Amt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Hiyuv_Exbank_Amt '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Hiyuv_Exbank_Amt)  AS  SUM,nmiss( Hiyuv_Exbank_Amt)  AS  Nr_Null,SUM(Hiyuv_Exbank_Amt=0)  AS  Nr_0,SUM(Hiyuv_Exbank_Amt>0)  AS  Nr_POS,SUM(Hiyuv_Exbank_Amt<0   AND  Hiyuv_Exbank_Amt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Loan_Excard_Amt '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Loan_Excard_Amt)  AS  SUM,nmiss( Loan_Excard_Amt)  AS  Nr_Null,SUM(Loan_Excard_Amt=0)  AS  Nr_0,SUM(Loan_Excard_Amt>0)  AS  Nr_POS,SUM(Loan_Excard_Amt<0   AND  Loan_Excard_Amt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Hiyuv_Excard_Amt '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Hiyuv_Excard_Amt)  AS  SUM,nmiss( Hiyuv_Excard_Amt)  AS  Nr_Null,SUM(Hiyuv_Excard_Amt=0)  AS  Nr_0,SUM(Hiyuv_Excard_Amt>0)  AS  Nr_POS,SUM(Hiyuv_Excard_Amt<0   AND  Hiyuv_Excard_Amt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Loan_EM_Amt '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Loan_EM_Amt)  AS  SUM,nmiss( Loan_EM_Amt)  AS  Nr_Null,SUM(Loan_EM_Amt=0)  AS  Nr_0,SUM(Loan_EM_Amt>0)  AS  Nr_POS,SUM(Loan_EM_Amt<0   AND  Loan_EM_Amt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Hiyuv_EM_Amt '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Hiyuv_EM_Amt)  AS  SUM,nmiss( Hiyuv_EM_Amt)  AS  Nr_Null,SUM(Hiyuv_EM_Amt=0)  AS  Nr_0,SUM(Hiyuv_EM_Amt>0)  AS  Nr_POS,SUM(Hiyuv_EM_Amt<0   AND  Hiyuv_EM_Amt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Loan_Exliscar_Amt '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Loan_Exliscar_Amt)  AS  SUM,nmiss( Loan_Exliscar_Amt)  AS  Nr_Null,SUM(Loan_Exliscar_Amt=0)  AS  Nr_0,SUM(Loan_Exliscar_Amt>0)  AS  Nr_POS,SUM(Loan_Exliscar_Amt<0   AND  Loan_Exliscar_Amt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Hiyuv_Exliscar_Amt '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Hiyuv_Exliscar_Amt)  AS  SUM,nmiss( Hiyuv_Exliscar_Amt)  AS  Nr_Null,SUM(Hiyuv_Exliscar_Amt=0)  AS  Nr_0,SUM(Hiyuv_Exliscar_Amt>0)  AS  Nr_POS,SUM(Hiyuv_Exliscar_Amt<0   AND  Hiyuv_Exliscar_Amt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Mortage_L_Month_Mosdi '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Mortage_L_Month_Mosdi)  AS  SUM,nmiss( Sum_Mortage_L_Month_Mosdi)  AS  Nr_Null,SUM(Sum_Mortage_L_Month_Mosdi=0)  AS  Nr_0,SUM(Sum_Mortage_L_Month_Mosdi>0)  AS  Nr_POS,SUM(Sum_Mortage_L_Month_Mosdi<0   AND  Sum_Mortage_L_Month_Mosdi  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Avg_Payoff_Last_Month_Nashaf '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Avg_Payoff_Last_Month_Nashaf)  AS  SUM,nmiss( Avg_Payoff_Last_Month_Nashaf)  AS  Nr_Null,SUM(Avg_Payoff_Last_Month_Nashaf=0)  AS  Nr_0,SUM(Avg_Payoff_Last_Month_Nashaf>0)  AS  Nr_POS,SUM(Avg_Payoff_Last_Month_Nashaf<0   AND  Avg_Payoff_Last_Month_Nashaf  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_T_Shuvar '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_T_Shuvar)  AS  SUM,nmiss( Sum_T_Shuvar)  AS  Nr_Null,SUM(Sum_T_Shuvar=0)  AS  Nr_0,SUM(Sum_T_Shuvar>0)  AS  Nr_POS,SUM(Sum_T_Shuvar<0   AND  Sum_T_Shuvar  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Loans_Nashaf_Real '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Loans_Nashaf_Real)  AS  SUM,nmiss( Sum_Loans_Nashaf_Real)  AS  Nr_Null,SUM(Sum_Loans_Nashaf_Real=0)  AS  Nr_0,SUM(Sum_Loans_Nashaf_Real>0)  AS  Nr_POS,SUM(Sum_Loans_Nashaf_Real<0   AND  Sum_Loans_Nashaf_Real  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Loans_G_Real '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Loans_G_Real)  AS  SUM,nmiss( Sum_Loans_G_Real)  AS  Nr_Null,SUM(Sum_Loans_G_Real=0)  AS  Nr_0,SUM(Sum_Loans_G_Real>0)  AS  Nr_POS,SUM(Sum_Loans_G_Real<0   AND  Sum_Loans_G_Real  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Matach_Credit '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Matach_Credit)  AS  SUM,nmiss( Sum_Matach_Credit)  AS  Nr_Null,SUM(Sum_Matach_Credit=0)  AS  Nr_0,SUM(Sum_Matach_Credit>0)  AS  Nr_POS,SUM(Sum_Matach_Credit<0   AND  Sum_Matach_Credit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Matach_Debit '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Matach_Debit)  AS  SUM,nmiss( Sum_Matach_Debit)  AS  Nr_Null,SUM(Sum_Matach_Debit=0)  AS  Nr_0,SUM(Sum_Matach_Debit>0)  AS  Nr_POS,SUM(Sum_Matach_Debit<0   AND  Sum_Matach_Debit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Transosher_Debit '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Transosher_Debit)  AS  SUM,nmiss( Sum_Transosher_Debit)  AS  Nr_Null,SUM(Sum_Transosher_Debit=0)  AS  Nr_0,SUM(Sum_Transosher_Debit>0)  AS  Nr_POS,SUM(Sum_Transosher_Debit<0   AND  Sum_Transosher_Debit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Transosher_Credit '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Transosher_Credit)  AS  SUM,nmiss( Sum_Transosher_Credit)  AS  Nr_Null,SUM(Sum_Transosher_Credit=0)  AS  Nr_0,SUM(Sum_Transosher_Credit>0)  AS  Nr_POS,SUM(Sum_Transosher_Credit<0   AND  Sum_Transosher_Credit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Cc_M_12 '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Cc_M_12)  AS  SUM,nmiss( Sum_Cc_M_12)  AS  Nr_Null,SUM(Sum_Cc_M_12=0)  AS  Nr_0,SUM(Sum_Cc_M_12>0)  AS  Nr_POS,SUM(Sum_Cc_M_12<0   AND  Sum_Cc_M_12  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Cc_M_13 '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Cc_M_13)  AS  SUM,nmiss( Sum_Cc_M_13)  AS  Nr_Null,SUM(Sum_Cc_M_13=0)  AS  Nr_0,SUM(Sum_Cc_M_13>0)  AS  Nr_POS,SUM(Sum_Cc_M_13<0   AND  Sum_Cc_M_13  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Cc_M_14 '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Cc_M_14)  AS  SUM,nmiss( Sum_Cc_M_14)  AS  Nr_Null,SUM(Sum_Cc_M_14=0)  AS  Nr_0,SUM(Sum_Cc_M_14>0)  AS  Nr_POS,SUM(Sum_Cc_M_14<0   AND  Sum_Cc_M_14  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Cc_M_15 '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Cc_M_15)  AS  SUM,nmiss( Sum_Cc_M_15)  AS  Nr_Null,SUM(Sum_Cc_M_15=0)  AS  Nr_0,SUM(Sum_Cc_M_15>0)  AS  Nr_POS,SUM(Sum_Cc_M_15<0   AND  Sum_Cc_M_15  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Sum_Nizcard_Cdo '  AS   FIELD, 'continuous' as type,COUNT(*)  AS NR_TOTAL,SUM(Sum_Nizcard_Cdo)  AS  SUM,nmiss( Sum_Nizcard_Cdo)  AS  Nr_Null,SUM(Sum_Nizcard_Cdo=0)  AS  Nr_0,SUM(Sum_Nizcard_Cdo>0)  AS  Nr_POS,SUM(Sum_Nizcard_Cdo<0   AND  Sum_Nizcard_Cdo  ne  .)  AS  nr_neg from ttt     union all
SELECT     ' Nlp_Grp '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Nlp_Grp)  AS  SUM,nmiss( Nlp_Grp)  AS  Nr_Null,SUM(Nlp_Grp=0)  AS  Nr_0,SUM(Nlp_Grp>0)  AS  Nr_POS,SUM(Nlp_Grp<0   AND  Nlp_Grp  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Mortage_Last_Month '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Mortage_Last_Month)  AS  SUM,nmiss( Count_Mortage_Last_Month)  AS  Nr_Null,SUM(Count_Mortage_Last_Month=0)  AS  Nr_0,SUM(Count_Mortage_Last_Month>0)  AS  Nr_POS,SUM(Count_Mortage_Last_Month<0   AND  Count_Mortage_Last_Month  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Mortage_Last_Month_ '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Mortage_Last_Month_)  AS  SUM,nmiss( Count_Mortage_Last_Month_)  AS  Nr_Null,SUM(Count_Mortage_Last_Month_=0)  AS  Nr_0,SUM(Count_Mortage_Last_Month_>0)  AS  Nr_POS,SUM(Count_Mortage_Last_Month_<0   AND  Count_Mortage_Last_Month_  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Checs '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Checs)  AS  SUM,nmiss( Count_Checs)  AS  Nr_Null,SUM(Count_Checs=0)  AS  Nr_0,SUM(Count_Checs>0)  AS  Nr_POS,SUM(Count_Checs<0   AND  Count_Checs  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cash '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cash)  AS  SUM,nmiss( Count_Cash)  AS  Nr_Null,SUM(Count_Cash=0)  AS  Nr_0,SUM(Count_Cash>0)  AS  Nr_POS,SUM(Count_Cash<0   AND  Count_Cash  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Transfer '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Transfer)  AS  SUM,nmiss( Count_Transfer)  AS  Nr_Null,SUM(Count_Transfer=0)  AS  Nr_0,SUM(Count_Transfer>0)  AS  Nr_POS,SUM(Count_Transfer<0   AND  Count_Transfer  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Transfer_Same_Inv '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Transfer_Same_Inv)  AS  SUM,nmiss( Count_Transfer_Same_Inv)  AS  Nr_Null,SUM(Count_Transfer_Same_Inv=0)  AS  Nr_0,SUM(Count_Transfer_Same_Inv>0)  AS  Nr_POS,SUM(Count_Transfer_Same_Inv<0   AND  Count_Transfer_Same_Inv  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_G_Credit '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_G_Credit)  AS  SUM,nmiss( Count_G_Credit)  AS  Nr_Null,SUM(Count_G_Credit=0)  AS  Nr_0,SUM(Count_G_Credit>0)  AS  Nr_POS,SUM(Count_G_Credit<0   AND  Count_G_Credit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Checs_Debit '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Checs_Debit)  AS  SUM,nmiss( Count_Checs_Debit)  AS  Nr_Null,SUM(Count_Checs_Debit=0)  AS  Nr_0,SUM(Count_Checs_Debit>0)  AS  Nr_POS,SUM(Count_Checs_Debit<0   AND  Count_Checs_Debit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cash_Debit '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cash_Debit)  AS  SUM,nmiss( Count_Cash_Debit)  AS  Nr_Null,SUM(Count_Cash_Debit=0)  AS  Nr_0,SUM(Count_Cash_Debit>0)  AS  Nr_POS,SUM(Count_Cash_Debit<0   AND  Count_Cash_Debit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Transfer_Debit '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Transfer_Debit)  AS  SUM,nmiss( Count_Transfer_Debit)  AS  Nr_Null,SUM(Count_Transfer_Debit=0)  AS  Nr_0,SUM(Count_Transfer_Debit>0)  AS  Nr_POS,SUM(Count_Transfer_Debit<0   AND  Count_Transfer_Debit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Transfer_Debit_Same '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Transfer_Debit_Same)  AS  SUM,nmiss( Count_Transfer_Debit_Same)  AS  Nr_Null,SUM(Count_Transfer_Debit_Same=0)  AS  Nr_0,SUM(Count_Transfer_Debit_Same>0)  AS  Nr_POS,SUM(Count_Transfer_Debit_Same<0   AND  Count_Transfer_Debit_Same  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_G_Debit '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_G_Debit)  AS  SUM,nmiss( Count_G_Debit)  AS  Nr_Null,SUM(Count_G_Debit=0)  AS  Nr_0,SUM(Count_G_Debit>0)  AS  Nr_POS,SUM(Count_G_Debit<0   AND  Count_G_Debit  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Loans_Last_Month_Nashaf '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Loans_Last_Month_Nashaf)  AS  SUM,nmiss( Count_Loans_Last_Month_Nashaf)  AS  Nr_Null,SUM(Count_Loans_Last_Month_Nashaf=0)  AS  Nr_0,SUM(Count_Loans_Last_Month_Nashaf>0)  AS  Nr_POS,SUM(Count_Loans_Last_Month_Nashaf<0   AND  Count_Loans_Last_Month_Nashaf  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_1 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_1)  AS  SUM,nmiss( Count_Cc_M_1)  AS  Nr_Null,SUM(Count_Cc_M_1=0)  AS  Nr_0,SUM(Count_Cc_M_1>0)  AS  Nr_POS,SUM(Count_Cc_M_1<0   AND  Count_Cc_M_1  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_2 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_2)  AS  SUM,nmiss( Count_Cc_M_2)  AS  Nr_Null,SUM(Count_Cc_M_2=0)  AS  Nr_0,SUM(Count_Cc_M_2>0)  AS  Nr_POS,SUM(Count_Cc_M_2<0   AND  Count_Cc_M_2  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_3 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_3)  AS  SUM,nmiss( Count_Cc_M_3)  AS  Nr_Null,SUM(Count_Cc_M_3=0)  AS  Nr_0,SUM(Count_Cc_M_3>0)  AS  Nr_POS,SUM(Count_Cc_M_3<0   AND  Count_Cc_M_3  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_4 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_4)  AS  SUM,nmiss( Count_Cc_M_4)  AS  Nr_Null,SUM(Count_Cc_M_4=0)  AS  Nr_0,SUM(Count_Cc_M_4>0)  AS  Nr_POS,SUM(Count_Cc_M_4<0   AND  Count_Cc_M_4  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_5 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_5)  AS  SUM,nmiss( Count_Cc_M_5)  AS  Nr_Null,SUM(Count_Cc_M_5=0)  AS  Nr_0,SUM(Count_Cc_M_5>0)  AS  Nr_POS,SUM(Count_Cc_M_5<0   AND  Count_Cc_M_5  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_6 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_6)  AS  SUM,nmiss( Count_Cc_M_6)  AS  Nr_Null,SUM(Count_Cc_M_6=0)  AS  Nr_0,SUM(Count_Cc_M_6>0)  AS  Nr_POS,SUM(Count_Cc_M_6<0   AND  Count_Cc_M_6  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_7 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_7)  AS  SUM,nmiss( Count_Cc_M_7)  AS  Nr_Null,SUM(Count_Cc_M_7=0)  AS  Nr_0,SUM(Count_Cc_M_7>0)  AS  Nr_POS,SUM(Count_Cc_M_7<0   AND  Count_Cc_M_7  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_8 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_8)  AS  SUM,nmiss( Count_Cc_M_8)  AS  Nr_Null,SUM(Count_Cc_M_8=0)  AS  Nr_0,SUM(Count_Cc_M_8>0)  AS  Nr_POS,SUM(Count_Cc_M_8<0   AND  Count_Cc_M_8  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_9 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_9)  AS  SUM,nmiss( Count_Cc_M_9)  AS  Nr_Null,SUM(Count_Cc_M_9=0)  AS  Nr_0,SUM(Count_Cc_M_9>0)  AS  Nr_POS,SUM(Count_Cc_M_9<0   AND  Count_Cc_M_9  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_10 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_10)  AS  SUM,nmiss( Count_Cc_M_10)  AS  Nr_Null,SUM(Count_Cc_M_10=0)  AS  Nr_0,SUM(Count_Cc_M_10>0)  AS  Nr_POS,SUM(Count_Cc_M_10<0   AND  Count_Cc_M_10  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_11 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_11)  AS  SUM,nmiss( Count_Cc_M_11)  AS  Nr_Null,SUM(Count_Cc_M_11=0)  AS  Nr_0,SUM(Count_Cc_M_11>0)  AS  Nr_POS,SUM(Count_Cc_M_11<0   AND  Count_Cc_M_11  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Loan_Exbank_Cnt '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Loan_Exbank_Cnt)  AS  SUM,nmiss( Loan_Exbank_Cnt)  AS  Nr_Null,SUM(Loan_Exbank_Cnt=0)  AS  Nr_0,SUM(Loan_Exbank_Cnt>0)  AS  Nr_POS,SUM(Loan_Exbank_Cnt<0   AND  Loan_Exbank_Cnt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Hiyuv_Exbank_Cnt '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Hiyuv_Exbank_Cnt)  AS  SUM,nmiss( Hiyuv_Exbank_Cnt)  AS  Nr_Null,SUM(Hiyuv_Exbank_Cnt=0)  AS  Nr_0,SUM(Hiyuv_Exbank_Cnt>0)  AS  Nr_POS,SUM(Hiyuv_Exbank_Cnt<0   AND  Hiyuv_Exbank_Cnt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Loan_Excard_Cnt '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Loan_Excard_Cnt)  AS  SUM,nmiss( Loan_Excard_Cnt)  AS  Nr_Null,SUM(Loan_Excard_Cnt=0)  AS  Nr_0,SUM(Loan_Excard_Cnt>0)  AS  Nr_POS,SUM(Loan_Excard_Cnt<0   AND  Loan_Excard_Cnt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Hiyuv_Excard_Cnt '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Hiyuv_Excard_Cnt)  AS  SUM,nmiss( Hiyuv_Excard_Cnt)  AS  Nr_Null,SUM(Hiyuv_Excard_Cnt=0)  AS  Nr_0,SUM(Hiyuv_Excard_Cnt>0)  AS  Nr_POS,SUM(Hiyuv_Excard_Cnt<0   AND  Hiyuv_Excard_Cnt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Loan_EM_Cnt '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Loan_EM_Cnt)  AS  SUM,nmiss( Loan_EM_Cnt)  AS  Nr_Null,SUM(Loan_EM_Cnt=0)  AS  Nr_0,SUM(Loan_EM_Cnt>0)  AS  Nr_POS,SUM(Loan_EM_Cnt<0   AND  Loan_EM_Cnt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Hiyuv_EM_Cnt '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Hiyuv_EM_Cnt)  AS  SUM,nmiss( Hiyuv_EM_Cnt)  AS  Nr_Null,SUM(Hiyuv_EM_Cnt=0)  AS  Nr_0,SUM(Hiyuv_EM_Cnt>0)  AS  Nr_POS,SUM(Hiyuv_EM_Cnt<0   AND  Hiyuv_EM_Cnt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Loan_Exliscar_Cnt '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Loan_Exliscar_Cnt)  AS  SUM,nmiss( Loan_Exliscar_Cnt)  AS  Nr_Null,SUM(Loan_Exliscar_Cnt=0)  AS  Nr_0,SUM(Loan_Exliscar_Cnt>0)  AS  Nr_POS,SUM(Loan_Exliscar_Cnt<0   AND  Loan_Exliscar_Cnt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Hiyuv_Exliscar_Cnt '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Hiyuv_Exliscar_Cnt)  AS  SUM,nmiss( Hiyuv_Exliscar_Cnt)  AS  Nr_Null,SUM(Hiyuv_Exliscar_Cnt=0)  AS  Nr_0,SUM(Hiyuv_Exliscar_Cnt>0)  AS  Nr_POS,SUM(Hiyuv_Exliscar_Cnt<0   AND  Hiyuv_Exliscar_Cnt  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Cnt_Mortage_L_Month_Mosdi '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Cnt_Mortage_L_Month_Mosdi)  AS  SUM,nmiss( Cnt_Mortage_L_Month_Mosdi)  AS  Nr_Null,SUM(Cnt_Mortage_L_Month_Mosdi=0)  AS  Nr_0,SUM(Cnt_Mortage_L_Month_Mosdi>0)  AS  Nr_POS,SUM(Cnt_Mortage_L_Month_Mosdi<0   AND  Cnt_Mortage_L_Month_Mosdi  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Cnt_Payoff_Last_Month_Nashaf '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Cnt_Payoff_Last_Month_Nashaf)  AS  SUM,nmiss( Cnt_Payoff_Last_Month_Nashaf)  AS  Nr_Null,SUM(Cnt_Payoff_Last_Month_Nashaf=0)  AS  Nr_0,SUM(Cnt_Payoff_Last_Month_Nashaf>0)  AS  Nr_POS,SUM(Cnt_Payoff_Last_Month_Nashaf<0   AND  Cnt_Payoff_Last_Month_Nashaf  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_12 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_12)  AS  SUM,nmiss( Count_Cc_M_12)  AS  Nr_Null,SUM(Count_Cc_M_12=0)  AS  Nr_0,SUM(Count_Cc_M_12>0)  AS  Nr_POS,SUM(Count_Cc_M_12<0   AND  Count_Cc_M_12  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_13 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_13)  AS  SUM,nmiss( Count_Cc_M_13)  AS  Nr_Null,SUM(Count_Cc_M_13=0)  AS  Nr_0,SUM(Count_Cc_M_13>0)  AS  Nr_POS,SUM(Count_Cc_M_13<0   AND  Count_Cc_M_13  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_14 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_14)  AS  SUM,nmiss( Count_Cc_M_14)  AS  Nr_Null,SUM(Count_Cc_M_14=0)  AS  Nr_0,SUM(Count_Cc_M_14>0)  AS  Nr_POS,SUM(Count_Cc_M_14<0   AND  Count_Cc_M_14  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Count_Cc_M_15 '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Count_Cc_M_15)  AS  SUM,nmiss( Count_Cc_M_15)  AS  Nr_Null,SUM(Count_Cc_M_15=0)  AS  Nr_0,SUM(Count_Cc_M_15>0)  AS  Nr_POS,SUM(Count_Cc_M_15<0   AND  Count_Cc_M_15  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' Risk_Score '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(Risk_Score)  AS  SUM,nmiss( Risk_Score)  AS  Nr_Null,SUM(Risk_Score=0)  AS  Nr_0,SUM(Risk_Score>0)  AS  Nr_POS,SUM(Risk_Score<0   AND  Risk_Score  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' E_Exbank '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(E_Exbank)  AS  SUM,nmiss( E_Exbank)  AS  Nr_Null,SUM(E_Exbank=0)  AS  Nr_0,SUM(E_Exbank>0)  AS  Nr_POS,SUM(E_Exbank<0   AND  E_Exbank  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' E_EM '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(E_EM)  AS  SUM,nmiss( E_EM)  AS  Nr_Null,SUM(E_EM=0)  AS  Nr_0,SUM(E_EM>0)  AS  Nr_POS,SUM(E_EM<0   AND  E_EM  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' E_Excard '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(E_Excard)  AS  SUM,nmiss( E_Excard)  AS  Nr_Null,SUM(E_Excard=0)  AS  Nr_0,SUM(E_Excard>0)  AS  Nr_POS,SUM(E_Excard<0   AND  E_Excard  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' E_Exliscar '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(E_Exliscar)  AS  SUM,nmiss( E_Exliscar)  AS  Nr_Null,SUM(E_Exliscar=0)  AS  Nr_0,SUM(E_Exliscar>0)  AS  Nr_POS,SUM(E_Exliscar<0   AND  E_Exliscar  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' E_G '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(E_G)  AS  SUM,nmiss( E_G)  AS  Nr_Null,SUM(E_G=0)  AS  Nr_0,SUM(E_G>0)  AS  Nr_POS,SUM(E_G<0   AND  E_G  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' E_Nashaf '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(E_Nashaf)  AS  SUM,nmiss( E_Nashaf)  AS  Nr_Null,SUM(E_Nashaf=0)  AS  Nr_0,SUM(E_Nashaf>0)  AS  Nr_POS,SUM(E_Nashaf<0   AND  E_Nashaf  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' E_Mrtg_ '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(E_Mrtg_)  AS  SUM,nmiss( E_Mrtg_)  AS  Nr_Null,SUM(E_Mrtg_=0)  AS  Nr_0,SUM(E_Mrtg_>0)  AS  Nr_POS,SUM(E_Mrtg_<0   AND  E_Mrtg_  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' E_Mrtg_Bnk_Ex '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(E_Mrtg_Bnk_Ex)  AS  SUM,nmiss( E_Mrtg_Bnk_Ex)  AS  Nr_Null,SUM(E_Mrtg_Bnk_Ex=0)  AS  Nr_0,SUM(E_Mrtg_Bnk_Ex>0)  AS  Nr_POS,SUM(E_Mrtg_Bnk_Ex<0   AND  E_Mrtg_Bnk_Ex  ne  .)  AS  nr_neg from ttt   union all
SELECT     ' E_Mrtg_Ex_Nobnk '  AS   FIELD, 'COUNT/DESCRETE' as type,COUNT(*)  AS NR_TOTAL,SUM(E_Mrtg_Ex_Nobnk)  AS  SUM,nmiss( E_Mrtg_Ex_Nobnk)  AS  Nr_Null,SUM(E_Mrtg_Ex_Nobnk=0)  AS  Nr_0,SUM(E_Mrtg_Ex_Nobnk>0)  AS  Nr_POS,SUM(E_Mrtg_Ex_Nobnk<0   AND  E_Mrtg_Ex_Nobnk  ne  .)  AS  nr_neg from ttt   
;
quit;

[Tom]

Transpose the data first.  If speed is of concern you might transpose the data using a data step view instead of actual PROC TRANSPOSE.  That way you do not need to store two copies of the data.

data tall / view=tall;
  set LastMon_Yasmintbl_Ortal_use_t  ;
  array cont Case_Id Avg_Payoff ..... ;
  do over cont ;
     length FIELD $32 ;
     field = vname(cont);
     value = cont;
     output;
  end;
  keep field value;
run;

Then you can write one query using a GROUP BY

proc sql ;
create table t_contunous_CONUT_Vars as
SELECT
     FIELD
   , 'continuous' as TYPE
   , count(*) as NR_OBS
   , count(value)  AS NR_NON_NULL
   , calculated NR_OBS - calculated NR_NON_NULL as NR_NULL
   , sum(value=0)  AS  NR_0
   , sum(value>0)  AS  NR_POS
   , calculated NR_NON_NULL - calculated NR_POS - calculated NR_0 as NR_NEG
  from TALL
  group by field
;
quit;

[kmw]

This is a sample written to drop variables when all values are missing. You could avoid submitting the last step where the variables are dropped, but you can see the list of variables with all missing values in the macro variable &mlist .

https://support.sas.com/kb/24/622.html

[Ksharp]

As @Tom pointed out, how do I know which variables are continuous,which variables are count ??
Once you get the following meta data .
field type
x1 continuous
c1 count
z1 char
you could use CALL EXECUTE to dynamically generated the code I showed you and running it .

[Ronein]

Thanks! Can you please show the full code that identify automatically var type and create the code that make the required calculations?
Also- the distinguish between continous and count was made by me and how sas know to distinguish between them???

[Ksharp]

I think it is easy for you since you have already used sas for so many years, isn't it ?

options missing=.;
Data have;
input CustID  x1 x2 x3 w1 w2  B1 B2 C1 $ C2 $ ;
cards;
1 10.1 15 20 0 2 0 0 abx .
2 . . 21.1 0 0 1 1 abx def 
3 12 14.4 16 . . 1 1  abx def
4 18 . 20.8 0 0 0 1 abx def
5 . . .  1 3 0 0 . def
;
run;

proc transpose data=have(obs=0 drop=CustID) out=vname;
var _all_;
run;
data vname;
 set vname;
 length type $ 40;
 if lowcase(_name_) in ('x1' 'x2' 'x3' 'x4') then type='continuous'; 
 if lowcase(_name_) in ('w1' 'w2') then type='count'; 
 if lowcase(_name_) in ('b1' 'b2') then type='binary'; 
 if lowcase(_name_) in ('c1' 'c2') then type='char'; 
run;
proc sort data=vname;
by type;
run;

title;
data _null_;
 set vname end=last;
 by type;
 if _n_=1 then call execute('proc sql;');

 if type in ('count' 'continuous') then call execute(catt(
"select '",_name_,"' as field,'",type,"' as type,nmiss(",_name_,") as nr_null,sum(",_name_,"=0) as nr_0,sum(",_name_,">0) as nr_pos  from have"
));

 if type='binary' then call execute(catt(
"select '",_name_,"' as field,'",type,"' as type,nmiss(",_name_,") as nr_null,sum(",_name_,"=0) as nr_0,sum(",_name_,"=1) as nr_1  from have"
));

 if type='char' then call execute(catt(
"select '",_name_,"' as field,'",type,"' as type,nmiss(",_name_,") as nr_null,n(",_name_,") as nr_not_null  from have"
));

if not last.type then call execute('union all');
 else call execute(';');
if last then call execute('quit;');
run;

[Ronein]

Thank you