topic Insert a Row at the end of the data with totals in SAS Programming

Insert a Row at the end of the data with totals

Ronein — Sat, 20 Feb 2021 19:42:22 GMT

Hello

In this example we create a total row and add it to the data set.

I want to ask some questions please:

1- Why Retain is not necesary here?

As I understand Retain telling SAS not to reset the variables to missing at the beginning of each iteration of the DATA step.

So why in this case the values are keeping from one row to next row?

2-At the first iteration ct has null value and then ct+count add null value with 23.

As far as I know when we use "+" to sum null with value then we get null.

So why in this case null+23 get 23?

data have;
input var $ COUNT;
cards;
DATA1 23
DATA2 35
DATA3 27
DATA4 24
DATA5 25
DATA6 22
DATA7 29
;
Run;

data want;
set have end=last;
ct+count;
output;
if last then do;
var='Total';
Count=CT;
output;
end;
drop ct;
run;

Re: Insert a Row at the end of the data with totals

Shmuel — Sat, 20 Feb 2021 20:18:48 GMT

1) AS CT is not an input variable and the accumulation is done by

ct+count;

then CT is implicitly retained.

In case you accumulate by ct=ct+coutnt or by ct=sum(ct, xount) - you'll need add a retain statement.

In all cases you add the retain to be on the safe side.

2) The usage of ct+count neglegts or skups null values.

Re: Insert a Row at the end of the data with totals

Tom — Sun, 21 Feb 2021 03:41:06 GMT

Please read about the SUM statement you are using. The documentation will answer both questions.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lestmtsref&docsetTarget=n1dfiqj146yi2cn1maeju9wo7ijs.htm&locale=en

Re: Insert a Row at the end of the data with totals

mkeintz — Sun, 21 Feb 2021 03:42:20 GMT

The statement

ct+count:

is what sas call a Sum Statement.

Among other points it says:

The variable is automatically set to 0 before SAS reads the first observation.

So take a look at the "Before" and "After" values of totage below, especially the before value for observation 1:

data _null_;
  put _n_=  'Before: ' totage= z2. @;
  set sashelp.class (obs=3);
  totage+age;
  put 'After: ' totage=;
run;

The other pertinent point, as you have discovered is:

The variable's value is retained from one iteration to the next, as if it had appeared in a RETAIN statement.

And finally, it functions like the SUM function, which will treat missing values like zeroes.