BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eric232
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
eric232
Obsidian | Level 7

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

19 REPLIES 19
PeterClemmensen
Tourmaline | Level 20

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

eric232
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
eric232
Obsidian | Level 7

 

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

eric232
Obsidian | Level 7

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;   

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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,

eric232
Obsidian | Level 7

Sorry, I had not read well your programm

Thank you

eric232
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

eric232
Obsidian | Level 7

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

Patrick
Opal | Level 21

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

eric232
Obsidian | Level 7

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)

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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