Add summary variable as last observation

[forumsguy]

Hi there,

I have following dataset

Aseries      Bseries      First_col  Name

27583         2300         00           Standard formula

162             .               1            Reinsurance

144             .               1A          Securitisations

326             .               2            Derivatives

320             .               3            Other risk mitigating contracts

166             .               4            Other credit exposures

Now I want in the last observation First_col ='Summary' and  Aseries=(27583-(162+144+326+320+166)) Name='Final' and Bseries='.' so my output should be

Aseries      Bseries      First_col  Name

27583         2300         00           Standard formula

162             .               1            Reinsurance

144             .               1A          Securitisations

326             .               2            Derivatives

320             .               3            Other risk mitigating contracts

166             .               4            Other credit exposures

26465         .               Sum       Final

How can this be done in Datastep. I am able to find the total sum of Aseries but how to select 1st observation and then do calculations on it ??? may be I am missing some  Output statements. Also one more question ??? can this be done  in proc report itself.

[art297 · Accepted solution]

I can offer a possibility in answer to your first question:

data have;

  informat name $50.;

  input Aseries      Bseries    First_col $  Name &;

  cards;

27583 2300 00 Standard formula

162     .  1  Reinsurance

144     .  1A Securitisations

326     .  2  Derivatives

320     .  3  Other risk mitigating contracts

166     .  4  Other credit exposures

;

data want (drop=total);

  set have end=eof;

  if First_col='00' then total=Aseries;

  else total+-1*Aseries;

  output;

  if eof then do;

    Aseries=total;

          First_col='Sum';

    Name='Final';

    output;

  end;

run;

[forumsguy]

WOW.... Fantastic..... Art the only thing I did not understand is the 

else total+-1*Aseries;

this  part. What is the significance of it ???  specifically (total +-1 ) what does it imply to ???? is it total=total +(total-1) * Aserries ???

further explaination will help me sharpen my basics.

[art297]

I am not a programmer, per se thus, even though it was my code, I too could use an explanation from someone more knowledgeable.

What I can tell you is that a statement of the form:

  x+something

has an effect like using the sum function.  That is, it adds the something to x and retains the value of x across subsequent records.  Since I don't think that the form of statement is legal if expressed as:

x-something

I simply used x+-1*something which still fits within the x+something structure, but is adding -1*something to x which is, in effect, subtracting the value of something from x, not negating the total if it confronts a missing value, and automatically retaining the sum across records.

If my explanation isn't sufficient, I'm sure that someone will correct me.

[Linlin]

Hi Art,

I like your code. I replace "if First_col='00'" with "if _n_ =1";

if First_col='00' then total=Aseries;

  else total+-1*Aseries;

  output;

  if eof then do;

    Aseries=total;

          First_col='Sum';

    Name='Final';

    output;

  end;

run;

Linlin