topic How to replace Null value with Zero in New SAS User

How to Replace Null value with Zero

NikitaA — Wed, 09 Nov 2022 13:24:27 GMT

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

Re: How to Replace Null value with Zero

PeterClemmensen — Wed, 09 Nov 2022 13:37:14 GMT

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.

Re: How to Replace Null value with Zero

ballardw — Wed, 09 Nov 2022 15:32:30 GMT

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

Re: How to Replace Null value with Zero

PaigeMiller — Wed, 09 Nov 2022 15:38:59 GMT

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.

Re: How to Replace Null value with Zero

Reeza — Wed, 09 Nov 2022 16:07:13 GMT

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.

Re: How to Replace Null value with Zero

NikitaA — Thu, 10 Nov 2022 09:15:07 GMT

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

Thanks!

Re: How to Replace Null value with Zero

NikitaA — Thu, 10 Nov 2022 09:17:07 GMT

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

Thanks

Re: How to Replace Null value with Zero

Kurt_Bremser — Thu, 10 Nov 2022 09:39:35 GMT

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.

How to replace Null value with Zero

NikitaA — Thu, 10 Nov 2022 09:40:59 GMT

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

Re: How to Replace Null value with Zero

NikitaA — Thu, 10 Nov 2022 09:55:57 GMT

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!

Re: How to Replace Null value with Zero

Kurt_Bremser — Thu, 10 Nov 2022 10:23:19 GMT

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.

Re: How to replace Null value with Zero

Kurt_Bremser — Thu, 10 Nov 2022 10:24:13 GMT

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