Projection - Need some help with first/last and do syntax

[PeterEskild]

Hi everybody. The old thread became to long and I will rather take one problem at a time. I have attached program that generates the data. Tried uploading a dataset but it wasn't allowed. I have also put the code below.

Transtype=1 is the aqusitions price and when type=3 it's a depreciation value. I have to depreciate each ID until the cumprofit is 0.How the ID's have been depreciated over time has changed so somtimes the acquisitions price is negative and therefore the depreciating value is positive. For the most part it's the other way around. The depreciation value is the acquisitions price divided by lifetime.

Below is the code. I have made one program for when the aqusitions price is negative and another when it's positive. That's why I have the where statement in the beginning. If I just run it on the whole code it's a never ending loop.

So I hope you can help me with two things:

- Make one program instead of two that take account of when the aqusitions price is negative and the other way around. It's only some minus's and plus's that makes the difference between the two programs.

- Those new ID's which only have one record where type=1 aren't depreciated with the code below. I don't know why since the amount to be depreciated with is calculated but no records are made. Those are ID L7, N1 and M1.

I truly hope somebody can help. Thanks in advance.

data projecting5;

length id $4.;

set projecting4

(where=(id in('B9','G4','L7','M1','N1','P6','R3','S1','T2','U5')));

by id;

if first.id then do;

          NEW='EXISTING';

          i=0;

          if type=1 then do;

                      rest=profit;

                      dep=profit/lifetime;

                      output;

    end;

          else do;

                      put "ERROR: " id=;

                      stop;

          end;

end;

else do;

          if not last.id then do;

                      tdate=trans_date;

                      rest=rest+profit;

                      output;

          end;

          else do while (rest>=0);

                      i=i+1;

                      NEW='NEW';

                      rest=rest+profit;

                      trans_date=intnx('month',tdate,i,'E');

                      if rest>=0 then output;

  end;

            if rest<0 then do;

                      profit=-rest+profit;

                      rest=0;

                      output;

          end;

end;

format rest profit dep  commax14.2 tdate ddmmyy10.;

retain i dep rest new tdate type;

keep id profit trans_date rest new dep type;

run;

******Depreciation for where transtype=1 is negative***********;

data projecting5_NEG;

length id $4.;

set projecting4

(where=(id in('W1','J1')));

by id;

if first.id then do;

          NEW='EXISTING';

          i=0;

          if type=1 then do;

                      rest=profit;

                      dep=-profit/lifetime;

                      output;

    end;

          else do;

                      put "ERROR: " id=;

                      stop;

          end;

end;

else do;

          if not last.id then do;

                      tdate=trans_date;

                      rest=rest+profit;

                      output;

          end;

          else do while (rest<=0);

                      i=i+1;

                      NEW='NEW';

                      rest=rest+profit;

                      trans_date=intnx('month',tdate,i,'E');

                      if rest<=0 then output;

  end;

            if rest>0 then do;

                      profit=rest-profit;

                      rest=0;

                      output;

                      end;

end;

format rest profit dep  commax14.2 tdate ddmmyy10.;

retain i dep rest new tdate type;

keep id profit trans_date rest new dep type;

run;

[RW9]

Hi,

There are a few things which jump out.  In the second bit of code, you have a typo: dep=-profit/lifetime;

The minus before profit?  Also, the do loop (rest <= 0), is probably where your problem lies.  Not sure what rest is firs off, but if its > 0 then the loop condition will never be fullfilled as rest=rest+profit, so generally this would increase no?  You can condense your code by a few methods.  First assigning a letter to the input datasets (in my case a and b) and using that in an if:

data projecting5_NEG;

length id $4.;

set projecting4 (where=(id in('W1','J1')) in=a)

  projecting4 (where=(id in('B9','G4','L7','M1','N1','P6','R3','S1','T2','U5')) in=b);

;

by id;

if first.id then do;

          NEW='EXISTING';

          i=0;

          if type=1 then do;

                      rest=profit;

                      dep=-profit/lifetime;

                      output;

    end;

          else do;

                      put "ERROR: " id=;

                      stop;

          end;

end;

else do;

          if not last.id then do;

                      tdate=trans_date;

                      rest=rest+profit;

                      output;

          end;

   else if a then do;

  do while (rest<=0);

                      i=i+1;

                      NEW='NEW';

                      rest=rest+profit;

                      trans_date=intnx('month',tdate,i,'E');

                      if rest<=0 then output;

  end;

  else do while (rest>=0);

                      i=i+1;

                      NEW='NEW';

                      rest=rest+profit;

                      trans_date=intnx('month',tdate,i,'E');

                      if rest>=0 then output;

  end;

        if rest>0 then do;

                      profit=rest-profit;

                      rest=0;

                      output;

        end;

end;

format rest profit dep  commax14.2 tdate ddmmyy10.;

retain i dep rest new tdate type;

keep id profit trans_date rest new dep type;

run;

[PeterEskild]

Hi RW9. Thanks for your reply. I can use that. Yes it's a type. When the acquisitions price is postive and some times negative the code differs in the following way. See below.

I can always later make a macro or something that analyzes if it's a positive or negative acquisition price and then alter the plus/minus signs.

I can't see why L7','M1','N1 aren't projected. What should I alter in the code so they also are depreciated.

else do while (rest<=0);

                      i=i+1;

                      NEW='NEW';

                      rest=rest+profit;

                      trans_date=intnx('month',tdate,i,'E');

                      if rest>=0 then output;

else do while (rest<=0);

                      i=i+1;

                      NEW='NEW';

                      rest=rest+profit;

                      trans_date=intnx('month',tdate,i,'E');

                      if rest<=0 then output;

if rest<0 then do;

                      profit=-rest+profit;

                      rest=0;

                      output;

if rest>0 then do;

                      profit=rest-profit;

   rest=0;

                      output;

                      end;

[RW9]

Ah yes.  If you look at your main if statement, if first.id then do *else* do.  You will see that L7 only has one record.  Hence it is processed in the first if block, but it doesn't get processed in the else.  (Note that its very useful to align if/ends as it makes it easier to read)

data projecting5_NEG;

  length id $4.;

  set projecting4 (where=(id in('W1','J1')) in=a)

      projecting4 (where=(id in('B9','G4','L7','M1','N1','P6','R3','S1','T2','U5')) in=b);

  by id;

  if first.id then do;   /* L7 is processed here */

    NEW='EXISTING';

    i=0;

    if type=1 then do;

      rest=profit;

      dep=-profit/lifetime;

      output;

    end;

    else do;

      put "ERROR: " id=;

      stop;

    end;

  end;

  else do;  /* But not here as the record is first! */ 

    if not last.id then do;

      tdate=trans_date;

      rest=rest+profit;

      output;

    end;

    else if a then do;

      do while (rest<=0);

      i=i+1;

      NEW='NEW';

      rest=rest+profit;

      trans_date=intnx('month',tdate,i,'E');

      if rest<=0 then output;

      end;

    end;

    else do while (rest>=0);

      i=i+1;

      NEW='NEW';

      rest=rest+profit;

      trans_date=intnx('month',tdate,i,'E');

      if rest>=0 then output;

    end;

    if rest>0 then do;

      profit=rest-profit;

      rest=0;

      output;

    end;

  end;

  format rest profit dep  commax14.2 tdate ddmmyy10.;

  retain i dep rest new tdate type;

  keep id profit trans_date rest new dep type;

run;

[PeterEskild]

Hi RW9. Thank you for the answer. I have to think of how to do it. Not that easy How would you do it?

[RW9]

Well, start by identify the logic.  So, you have an ID with only one record.  What would you do in this instance?  Currently the wholse of the second part of the if is ignored for these rows, what exactly is the "else" to avoid?  Maybe take the else out and let the second part of the code run on everything, however for row 1 your likely then to get two rows output.  Perhaps you could have another if that says if first.id and last.id then do ...; else do...;