topic Add summary variable as last observation in SAS Programming

Add summary variable as last observation

forumsguy — Mon, 09 Jan 2012 11:05:20 GMT

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.

Add summary variable as last observation

art297 — Mon, 09 Jan 2012 14:10:13 GMT

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;

Add summary variable as last observation

forumsguy — Mon, 09 Jan 2012 16:03:20 GMT

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.

Add summary variable as last observation

art297 — Mon, 09 Jan 2012 16:14:10 GMT

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.

Re: Add summary variable as last observation

Linlin — Tue, 10 Jan 2012 12:17:01 GMT

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