## Add summary variable as last observation

Solved
Frequent Contributor
Posts: 85

# Add summary variable as last observation

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.

Accepted Solutions
Solution
‎01-09-2012 09:10 AM
PROC Star
Posts: 8,163

## Add summary variable as last observation

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;

All Replies
Solution
‎01-09-2012 09:10 AM
PROC Star
Posts: 8,163

## Add summary variable as last observation

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;

Frequent Contributor
Posts: 85

## Add summary variable as last observation

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.

PROC Star
Posts: 8,163

## Add summary variable as last observation

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.

Super Contributor
Posts: 1,636

## Re: Add summary variable as last observation

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

🔒 This topic is solved and locked.