BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
forumsguy
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

4 REPLIES 4
art297
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 796 views
  • 1 like
  • 3 in conversation