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.
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;
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;
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.