topic Re: Sum with missing value in SAS Programming

Sum with missing value

eric232 — Mon, 26 Jun 2017 09:51:51 GMT

Hi!

proc sql;
create table Tab14_16GrpNiv as select KNIV, "" as typdip, groupe,
sum(tab14_16unionRep._2016total) as NbRep16,
sum(tab14_16unionRep._2015total) as NbRep15,
sum(tab14_16unionRep._2014total) as NbRep14,
sum(tab14_16unionRep._2016total) + sum(tab14_16unionRep._2015total) + sum(tab14_16unionRep._2014total) as NbRep14_16
from tab14_16unionSort inner join tab14_16unionRep on tab14_16unionSort.code = tab14_16unionRep.code
Group by KNIV, "", groupe;
quit;

Example : NbRep16 = . , NbRep15 = . and NbRep14 = 45 ---> The result is NbRep14_16 = .

But I woud like have NbRep14_16 = 45 and not "."

How to do ? Thanks

Re: Sum with missing value

PeterClemmensen — Mon, 26 Jun 2017 09:54:52 GMT

So you want missing values to be treated as zeros in your summation?

Re: Sum with missing value

RW9 — Mon, 26 Jun 2017 10:02:07 GMT

Use sum() function:

proc sql;
  create table Tab14_16GrpNiv as 
  select  kniv, 
          "" as typdip, 
          groupe,
          sum(tab14_16unionRep._2016total) as NbRep16,
          sum(tab14_16unionRep._2015total) as NbRep15,
          sum(tab14_16unionRep._2014total) as NbRep14,
          sum(calculated nbrep16,calculated nbrep15,calculated nbrep14)  as NbRep14_16  
  from    tab14_16unionSort  
  inner join tab14_16unionRep 
  on      tab14_16unionSort.code = tab14_16unionRep.code
  group by kniv,
           groupe;
quit;

Re: Sum with missing value

eric232 — Mon, 26 Jun 2017 10:26:03 GMT

Yes, treated as zeros in the summation, but not replaced by 0 in the columns

Re: Sum with missing value

eric232 — Mon, 26 Jun 2017 10:24:46 GMT

RW9

I don't understans ! I used sum() function... What is the diference between my program and your answer ?

Re: Sum with missing value

RW9 — Mon, 26 Jun 2017 10:28:21 GMT

You used the + operator on nbrep14_16:

. + . + 45 = .

The sum() function however sums all present values:

sum(.,.,45) = 45

Per the documentation:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000245953.htm

Re: Sum with missing value

eric232 — Mon, 26 Jun 2017 10:37:36 GMT

I tried this, but grouping does not happen, I get the same number of lines, KNIV*groupe are repeated

proc sql;
create table Tab14_16GrpNiv1 as select KNIV, "" as typdip, groupe,
sum(tab14_16unionRep._2016total) as NbRep16,
sum(tab14_16unionRep._2015total) as NbRep15,
sum(tab14_16unionRep._2014total) as NbRep14,
sum(tab14_16unionRep._2016total, tab14_16unionRep._2015total, tab14_16unionRep._2014total) as NbRep14_16
from tab14_16unionSort inner join tab14_16unionRep on tab14_16unionSort.code = tab14_16unionRep.code
Group by KNIV, "", groupe;
quit;

Re: Sum with missing value

RW9 — Mon, 26 Jun 2017 10:38:47 GMT

Pleas re-read my code, particularly the keyword calculated.

Also, its a good idea to follow the guidance you will find next to the Post button on new questions. Provide example test data in the form of a datastep so that we can a) see the structure, and b) have something to run against. Also posting example required output is a good idea.

There is also a code window - {i} above the post area to put code in so that formatting is reatained, this makes code much easier to read,

Re: Sum with missing value

eric232 — Mon, 26 Jun 2017 10:48:56 GMT

Sorry, I had not read well your programm

Thank you

Re: Sum with missing value

eric232 — Mon, 26 Jun 2017 11:00:48 GMT

I'm not english spoken, and I don't understand when you say " you will find next to the Post button on new questions" ... I understood the rest

Re: Sum with missing value

RW9 — Mon, 26 Jun 2017 12:03:12 GMT

No problems. When you start a new topic, below the post area is a couple of buttons, Post and Cancel. Should look like this:

Principally the third element. Include code and data (as a datastep with datalines), so that we have something to run against.

Re: Sum with missing value

eric232 — Mon, 26 Jun 2017 12:36:55 GMT

In reality, I want to show only the total columns because there is a lot of columns

Here, the grouping doesn't work

sum(tab14_16unionRep._2016total, tab14_16unionRep._2015total, tab14_16unionRep._2014total) as NbRep14_16

Here, the grouping is OK, but there is no summation if no values

sum(tab14_16unionRep._2016total) + sum(tab14_16unionRep._2015total) + sum(tab14_16unionRep._2014total) as NbRep14_16

Re: Sum with missing value

Patrick — Mon, 26 Jun 2017 22:46:09 GMT

@eric232

As @RW9 suggested to you:

1. Provide a data step which creates sample data

2. Explain us what you want and what's currently not working

3. Show us the desired result based on the sample data you've provided

If you ask questions this way then you put us into a situation where we can much better understand what you have and what you need AND you give us the data so we can provide you with tested code which actually creates your desired result.

Asking questions this way takes you a bit more time but it will reduce missunderstandings significantly and you'll get normally the "right" answer much quicker.

Re: Sum with missing value

eric232 — Mon, 26 Jun 2017 13:48:24 GMT

data tab14_16unionRep;

input code KNIV groupe _2016total _2015total _2014total;

datalines;

01022701 5 227 9 8 .

01033401 5 227 . 7 12

32022710 3 227 21 31 11

32024002 3 240 12 . .

32024005 3 240 . 14 .

;

run;

data tab14_16unionSort;

input code _2016total _2015total _2014total;

datalines;

01022701 11.12 13.25 .

01033401 . 10.23 15.21

32022710 21.24 31.68 11.47

32024002 13.36 . .

32024005 . 15.11 .

;

run;

The result I need :

5 227 36

3 227 63

3 240 26

What my 2 programs done :

proc sql;
create table Tab14_16GrpNiv2 as select KNIV, groupe,
/* sum(tab14_16unionRep._2016total, tab14_16unionRep._2015total, tab14_16unionRep._2014total) as NbRep14_16 */
sum(tab14_16unionRep._2016total) + sum(tab14_16unionRep._2015total) + sum(tab14_16unionRep._2014total) as NbRep14_16
from tab14_16unionSort inner join tab14_16unionRep on tab14_16unionSort.diplome_pre = tab14_16unionRep.diplome_pre
Group by KNIV, groupe;
quit;

5 227 17

5 227 19

3 227 63

3 240 12

3 240 14

5 227 36

3 227 63

3 240 .

PS : Providing a data set allowed me understand better (I'm a beginner in SAS, but no in MS Access SQL)

Re: Sum with missing value

RW9 — Mon, 26 Jun 2017 14:25:35 GMT

Well, I have no idea what the second dataset is for. This code gets you the desired output:

data tab14_16unionRep;
  input code kniv groupe _2016total _2015total _2014total;
datalines;
01022701 5 227 9 8 .
01033401 5 227 . 7 12
32022710 3 227 21 31 11
32024002 3 240 12 . .
32024005 3 240 . 14 .
;
run;

proc sql;
  create table WANT as
  select  KNIV,
          GROUPE,
          sum(ROW) as RESULT
  from    (select  KNIV,
                   GROUPE,
                   sum(_2016TOTAL,_2015TOTAL,_2014TOTAL) as ROW
           from    TAB14_16UNIONREP)
  group by KNIV,
           GROUPE;
quit;

Re: Sum with missing value

eric232 — Mon, 26 Jun 2017 15:35:40 GMT

Thank you very much. Il will use these two requests.

Does this mean that SAS does not allow summation with a single request ? Because with Access SQL it's possible to use the function Nz to avoid the missing values

Re: Sum with missing value

RW9 — Mon, 26 Jun 2017 16:11:45 GMT

Sorry, its not clear what you are saying. The SQL I provided has the subquery as you are both summing across variables, and down through observations. This is a data modelling choice made by you and your data.

The function Nz you refer to in Access is a simplified case when statement - this is a logical assignment function, nothing to do with aggregates which is the basis of your question.

ANSI SQL (which all programs implement) support summation upon columns - this is called aggregation. If all your data were in columns then it would be a simple sum() however your data isn't:

01022701 5 227 9 8 .

01033401 5 227 . 7 12

As you can see from the above, you are both summing() 9, 8, ., and ., 7, 12 across the observations, then summing those two totals together in the group. A better modelling technique would have been to have the data as:

CODE KNIV GROUPE YEAR RESULT

01022701 5 227 2015 8

01022701 5 227 2016 9

...

The reason is twofold, firstly you can do summing simply then as you only have one column to sum() by your groups. Secondly from a data point of view you do not need to have obervations for 2014 first code and 2016 second code.

Re: Sum with missing value

Tom — Mon, 26 Jun 2017 17:55:26 GMT

To add multiple values together and ignore the missing values you can use the SAS function SUM(,). Note that this is a different function than that SQL aggregate function SUM(). In SQL you can take aggregates of a single variable, SUM(), MEAN(), etc. But the SAS function SUM(,), MEAN(,) etc are designed to work on a single observation and take TWO or MORE arguments. That is how you and the SAS compiler can tell them apart.

In your example you appear to what to sum the individual sums. So you could either use the SAS SUM(,) function to add the values generated by the SQL aggregate function SUM() or do the reverse.

In this example program the last three variables should have the same value which will be the sum of the first two variables.

proc sql ;
create table want as
  select sum(x) as sum_x
       , sum(Y) as sum_y
       , sum( sum(x) , sum(y) ) as sum_xy
       , sum( sum(x,y) ) as sum_xy2
       , sum( calculated sum_x, calculated sum_y) as sum_xy3
  from have
;
quit;

Re: Sum with missing value

eric232 — Tue, 27 Jun 2017 09:01:19 GMT

You are right, The function Nz have nothing to do with aggregates

Re: Sum with missing value

eric232 — Tue, 27 Jun 2017 09:02:49 GMT

Thank you, the two codes work

 sum( sum(x) , sum(y) ) as sum_xy
       , sum( sum(x,y) ) as sum_xy2