How to Replace Null value with Zero

[NikitaA]

Hello All,

I am modifying an existing code and facing Error - "the mean summary function requires a numeric argument"

The Code is:-

proc sql;
create table ECL_&report_mth._Sum as
select report_mth
,final_stage

,count(ECL) as volume

,sum (balance) as balance
,sum (ECL) as ECL
,mean(ECL) as avg_ECL
,sum (ECL_Post) as ECL_Post
,mean(ECL_Post) as avg_ECL_Post
,mean(PiT_PD) as PiT_PD
from ECL_&report_mth._Study2
group by 1,2;
quit;

I see Null Values in ECL_&report_mth._Study2 table so I guess this is related to it.

Can anyone please tell me what should be added in order to resolve this error???

[PeterClemmensen]

The Mean Funtion can handle missing/null values. The problem is that one of the variables being fed to the Mean Function is not numeric.

[ballardw]

By "null" value do you mean that you see TEXT of NULL in the data set? That very likely means that variable is not numeric as unless you do some work with custom formats SAS will not display missing (i.e. "null") values as the word Null.

---

@NikitaA wrote:

Hello All,

I am modifying an existing code and facing Error - "the mean summary function requires a numeric argument"

The Code is:-

proc sql;
create table ECL_&report_mth._Sum as
select report_mth
,final_stage

,count(ECL) as volume

,sum (balance) as balance
,sum (ECL) as ECL
,mean(ECL) as avg_ECL
,sum (ECL_Post) as ECL_Post
,mean(ECL_Post) as avg_ECL_Post
,mean(PiT_PD) as PiT_PD
from ECL_&report_mth._Study2
group by 1,2;
quit;

I see Null Values in ECL_&report_mth._Study2 table so I guess this is related to it.

Can anyone please tell me what should be added in order to resolve this error???

---

[NikitaA]

Yes, You're right! Can you please suggest how to fix this?

Thanks

[Kurt_Bremser]

You must find out why a variable supposed to be numeric is in fact character. You have to inspect the process which creates your dataset.

[NikitaA]

Ah I understand, thanks... We have recently experienced change in our process and started importing an excel file which has Null values and hence causing this problem.

Thanks Again!

[NikitaA]

Hello All, I am modifying an existing code and facing Error - "the mean summary function requires a numeric argument" The Code is:- proc sql; create table ECL_&report_mth._Sum as select report_mth ,final_stage ,count(ECL) as volume ,sum (balance) as balance ,sum (ECL) as ECL ,mean(ECL) as avg_ECL ,sum (ECL_Post) as ECL_Post ,mean(ECL_Post) as avg_ECL_Post ,mean(PiT_PD) as PiT_PD from ECL_&report_mth._Study2 group by 1,2; quit; I see Null Values in ECL_&report_mth._Study2 table so I guess this is related to it.

Can anyone please tell me How can I fix this error???  

[Kurt_Bremser]

I moved this back in here, as the question is still the same.

[Kurt_Bremser]

Excel files are notoriously bad as means for transporting data across environments. Use text files which you read with a data step, where you have total control over column attributes.

[PaigeMiller]

In addition to the good replies so far, do you understand that replace a null with a zero and then computing a mean produces and invalid and incorrect mean value? I strongly advise against this.

[Reeza]

proc sql;
create table ECL_&report_mth._Sum as
select report_mth
,final_stage

,count(ECL) as volume

,sum (balance) as balance
,sum (ECL) as ECL
,mean(ECL) as avg_ECL
,sum (ECL_Post) as ECL_Post
,mean(ECL_Post) as avg_ECL_Post
,mean(PiT_PD) as PiT_PD
from ECL_&report_mth._Study2
group by 1,2;
quit;

One of the variables in the MEAN statement (highlighted) is character and needs to be numeric. If so, you should also get errors on the SUM statements. PiT_PD is the only variable that's only on the MEAN statement so it could be that variable but it's easy enough to check them all.

[NikitaA]

Yes, that's true... Can you please suggest how to fix this??

Thanks!