DATA Step, Macro, Functions and more

Sum with missing value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Sum with missing value

[ Edited ]

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


Accepted Solutions
Solution
‎06-27-2017 05:03 AM
Occasional Contributor
Posts: 11

Re: Sum with missing value

Thank you, the two codes work

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

 

View solution in original post


All Replies
PROC Star
Posts: 746

Re: Sum with missing value

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

Occasional Contributor
Posts: 11

Re: Sum with missing value

[ Edited ]

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

Super User
Super User
Posts: 7,955

Re: Sum with missing value

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;
Occasional Contributor
Posts: 11

Re: Sum with missing value

 

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

Super User
Super User
Posts: 7,955

Re: Sum with missing value

[ Edited ]

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

Occasional Contributor
Posts: 11

Re: Sum with missing value

[ Edited ]

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;   

Super User
Super User
Posts: 7,955

Re: Sum with missing value

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,

Occasional Contributor
Posts: 11

Re: Sum with missing value

Sorry, I had not read well your programm

Thank you

Occasional Contributor
Posts: 11

Re: Sum with missing value

[ Edited ]

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

Super User
Super User
Posts: 7,955

Re: Sum with missing value

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

Capture.PNG

 

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

Occasional Contributor
Posts: 11

Re: Sum with missing value

[ Edited ]

Ok

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

Respected Advisor
Posts: 4,173

Re: Sum with missing value

[ Edited ]

@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.

Occasional Contributor
Posts: 11

Re: Sum with missing value

[ Edited ]

OK

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

or

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)

Super User
Super User
Posts: 7,955

Re: Sum with missing value

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;
☑ This topic is solved.

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

Discussion stats
  • 19 replies
  • 401 views
  • 0 likes
  • 5 in conversation