How to perform calculations using PROC SQL with missing data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

How to perform calculations using PROC SQL with missing data

Hello everyone,

I have a questions regarding PROC SQL and missing information. I am trying to add two variables together, divide them by a third variable, to create a completely new variable. One of the variables I am adding by is missing. This causes the final variable to be missing as well, even though the other variables have values. Is there a way around this? Here is my code:

proc sql;

  create table finaltable2 as

  select  site                                                                                      label='Site Number'

  ,total_subject                                                                                   label='Number of Subjects'

  ,SECs                                                                                             label='Number of SECs'

  ,DCFs                                                                                             label='Number of DCFs'

  ,((SECs + DCFs)/total_subject) as avgquery                                    label='Avg SEC/DCF per Subject' format=6.2

  from subjmain2

  order site;

quit;

The bold line of code is the one I am having trouble with. the value of SECs is missing, but DCFs and total_subject are not. SAS is stilling outputting avgquery as missing.

Any help would be greatly appreciated.

Thank you!

-Tyler


Accepted Solutions
Solution
‎09-04-2014 05:20 PM
Respected Advisor
Posts: 4,931

Re: How to perform calculations using PROC SQL with missing data

Posted in reply to tkallday33

Use the coalesce function, like this:

,((coalesce(SECs, 0) + coalesce(DCFs, 0))/total_subject) as avgquery 


PG

PG

View solution in original post


All Replies
Solution
‎09-04-2014 05:20 PM
Respected Advisor
Posts: 4,931

Re: How to perform calculations using PROC SQL with missing data

Posted in reply to tkallday33

Use the coalesce function, like this:

,((coalesce(SECs, 0) + coalesce(DCFs, 0))/total_subject) as avgquery 


PG

PG
Occasional Contributor
Posts: 9

Re: How to perform calculations using PROC SQL with missing data

Excellent thank you! Worked like a charm.

Trusted Advisor
Posts: 1,228

Re: How to perform calculations using PROC SQL with missing data

Posted in reply to tkallday33

Or

,sum(SECs,DCFs)/total_subject as avgquery                                  

Respected Advisor
Posts: 4,931

Re: How to perform calculations using PROC SQL with missing data

But beware, if both SECs and DCFs are missing, SUM will yield a missing value.

Use SUM(SECs, DCFs, 0) to avoid that.


PG

Message was edited by: PG

PG
Trusted Advisor
Posts: 1,228

Re: How to perform calculations using PROC SQL with missing data

Thank PG - True, but as per stated problem only SECs can have missing values.

Super Contributor
Posts: 308

Re: How to perform calculations using PROC SQL with missing data

Posted in reply to tkallday33

Hello,

Another solution:

/*Prepare some data*/

data have;
set sashelp.class;
if _N_=1 then height=.;
run;

proc sql;
select *,
(weight + case
height
when . then 0
else   height
    end)
/weight 
from have;
quit;

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 219 views
  • 7 likes
  • 4 in conversation