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
Thank you, the two codes work
sum( sum(x) , sum(y) ) as sum_xy
, sum( sum(x,y) ) as sum_xy2
So you want missing values to be treated as zeros in your summation?
Yes, treated as zeros in the summation, but not replaced by 0 in the columns
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;
I don't understans ! I used sum() function... What is the diference between my program and your answer ?
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
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;
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,
Sorry, I had not read well your programm
Thank you
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
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.
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
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.
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)
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.