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
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.
You are right, The function Nz have nothing to do with aggregates
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;
Thank you, the two codes work
sum( sum(x) , sum(y) ) as sum_xy
, sum( sum(x,y) ) as sum_xy2
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: