Macro for computing summation variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Macro for computing summation variable

 

Hi

I will sincerely appreciate if someone can help with the below issue:

I have the attached dataset snaphot of which is as below. I want to compute a variable that for each row first takes the value of  cumafd_h, divides this into current and all the lagged values of afd_h, multiplies by the correspding value of mb_h (current when takes the current afd_h and lagged when takes lagged afd_h), and then sums the entire series. I want to do this for alll the rows and by gvkey. For example:

- for the 3rd row (gvkey 1000, fyear 1972), the variable needs to be compuated as 1.38*(-7.05/-6.86)+1.30*(.19/-6.86)+1.42*((.)/(-6.86))

- for the 4th row (gvkey 1000, fyear 1973), the variable needs to be compuated as  0.82*(-0.37/-7.23))+1.38*(-7.05/-7.23)+1.30*(.19/-7.23)+1.42*((.)/(-7.23))

 

I also want to impose the restriction that whenever the value within the parrenthesis is <0 , the it is resert as 0. For example - for the 4th row, (.19/-7.23) needs to be reset to 0.

 

This variable also needs to be computed by group gvkey.

 

My apologies if it is a lot of work.

 

gvkeyfyearmb_hafd_hcumafd_h
100019701.42  
100019711.300.190.19
100019721.38-7.05-6.86
100019730.82-0.37-7.23
100019740.79-0.48-7.71
100019750.92-1.08-8.79
100019760.939.190.40
100019771.075.165.56
100119832.28  
100119841.250.270.27
100119851.6817.2417.51
100219720.75  
100319831.94  
100319841.11-0.25-0.25

Accepted Solutions
Solution
‎02-08-2017 03:30 PM
Trusted Advisor
Posts: 1,022

Re: Macro for computing summation variable

[ Edited ]

Assuming I have identified an error in your numbers, then this should work (10feb2017: deprecated, please see second version below)

 

data want (drop=neg_sum pos_sum);
  set have;
  by gvkey;

  retain pos_sum neg_sum;

  if first.gvkey then call missing(pos_sum,neg_sum);

  if sign(afd_h)=-1 then do;
  	neg_sum=sum(neg_sum,afd_h*mb_h);
	result=neg_sum/cumafd_h;
  end;
  else if sign(afd_h)= 1 then do;
	pos_sum=sum(pos_sum,afd_h*mb_h);
	result=pos_sum/cumafd_h;
  end;
run;

 

I leave the INCORRECT code above for those who saw this response previously.  The correct code, below, now properly identifies when the POS_SUM vs NEG_SUM should be used.  It produces the same results as @art297's elsewhere in this topic discussion.

 

data want (drop=neg_sum pos_sum);
  set have;
  by gvkey;

  retain pos_sum neg_sum;

  if first.gvkey then call missing(pos_sum,neg_sum);

  if sign(afd_h)=-1 then neg_sum=sum(neg_sum,afd_h*mb_h); else
  if sign(afd_h)= 1 then pos_sum=sum(pos_sum,afd_h*mb_h);

  if sign(cumafd_h)=-1 then result=neg_sum/cumafd_h; else
  if sign(cumafd_h)= 1 then result=pos_sum/cumafd_h;
  end;
run;

The neg_sum is the sum of products  of AFD_H*MB_H to be used when CUMAFD_H is negative.  pOS_SUM is the same to be used when  CUMAFD_H is positive. This implicitly sets the relevant parenthetical expressions (historic AFD_H divided by current CUMAFD_H) to zero when they would be negative.

View solution in original post


All Replies
PROC Star
Posts: 7,492

Re: Macro for computing summation variable

It would help if you showed the values you want computed for each row. Also, what do you want to call the new computed variable?

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 6

Re: Macro for computing summation variable

Hey Art

 

Thank you for your quick response.

I have computed values of the first few rows and called the variable wtdmb_h. 

Revised excel attached

 

Appreciate this.

Trusted Advisor
Posts: 1,022

Re: Macro for computing summation variable

[ Edited ]

Please tell me how you get 1.38215 for 1972 and 1.35329 for 1973.

 

I ask because 1972 includes the expression (.19/-6.86) which is negative and therefore should be reset to zero. But you do not appear to do so.

Solution
‎02-08-2017 03:30 PM
Trusted Advisor
Posts: 1,022

Re: Macro for computing summation variable

[ Edited ]

Assuming I have identified an error in your numbers, then this should work (10feb2017: deprecated, please see second version below)

 

data want (drop=neg_sum pos_sum);
  set have;
  by gvkey;

  retain pos_sum neg_sum;

  if first.gvkey then call missing(pos_sum,neg_sum);

  if sign(afd_h)=-1 then do;
  	neg_sum=sum(neg_sum,afd_h*mb_h);
	result=neg_sum/cumafd_h;
  end;
  else if sign(afd_h)= 1 then do;
	pos_sum=sum(pos_sum,afd_h*mb_h);
	result=pos_sum/cumafd_h;
  end;
run;

 

I leave the INCORRECT code above for those who saw this response previously.  The correct code, below, now properly identifies when the POS_SUM vs NEG_SUM should be used.  It produces the same results as @art297's elsewhere in this topic discussion.

 

data want (drop=neg_sum pos_sum);
  set have;
  by gvkey;

  retain pos_sum neg_sum;

  if first.gvkey then call missing(pos_sum,neg_sum);

  if sign(afd_h)=-1 then neg_sum=sum(neg_sum,afd_h*mb_h); else
  if sign(afd_h)= 1 then pos_sum=sum(pos_sum,afd_h*mb_h);

  if sign(cumafd_h)=-1 then result=neg_sum/cumafd_h; else
  if sign(cumafd_h)= 1 then result=pos_sum/cumafd_h;
  end;
run;

The neg_sum is the sum of products  of AFD_H*MB_H to be used when CUMAFD_H is negative.  pOS_SUM is the same to be used when  CUMAFD_H is positive. This implicitly sets the relevant parenthetical expressions (historic AFD_H divided by current CUMAFD_H) to zero when they would be negative.

Occasional Contributor
Posts: 6

Re: Macro for computing summation variable

Hi mkeintz

 

Sorry for my late reply. Yes are absolutely right. My computation for 1972 was wrong. I ran your codes and it seems to work perfectly. Thank you very much.

Occasional Contributor
Posts: 6

Re: Macro for computing summation variable

Hi mkeinthz

 

I was looking at the output randomly and also went over the logic of your codes again. I want to reset negative value of (afd_h/cumafd_h) to zero. It seems you are separating the neg and pos based on only afd_h. It seems not to address the cases when afd_h and cumafd_h are individually negative leading to a pos value for (afd_h/cumafd_h). I am looking into this again.

 

I do not expect you to fix this for me. For now I am good. If I fix this I will let you know. But for now it seems art's codes address those cases I mentioned.

 

Thanks

Trusted Advisor
Posts: 1,022

Re: Macro for computing summation variable


anisfiu wrote:

Hi mkeinthz

 

I was looking at the output randomly and also went over the logic of your codes again. I want to reset negative value of (afd_h/cumafd_h) to zero. It seems you are separating the neg and pos based on only afd_h. It seems not to address the cases when afd_h and cumafd_h are individually negative leading to a pos value for (afd_h/cumafd_h). I am looking into this again.

 

I do not expect you to fix this for me. For now I am good. If I fix this I will let you know. But for now it seems art's codes address those cases I mentioned.

 

Thanks


@anisfiu

 

I believe you are correct, and that my code submitted earlier will produce erroneous results when sign(cumafd_h) ^= sign(afd_h).  Don't know how I missed that.  I'll edit my other post to include what I believe should be the correct code.  It now completely matches what @art297's code produces.  However, it does still offer some efficiencies in that it keeps 2 running totals instead of recalculating them with each new record.

 

Occasional Contributor
Posts: 6

Re: Macro for computing summation variable

Thank you mkeintz.

PROC Star
Posts: 7,492

Re: Macro for computing summation variable

This gets the same results as @mkeintz's code, thus you should mark his as being the correct answer. My version runs slower, but might save you the time for checking whether his calculations follow your logic.

 

data want (drop=counter i);
  set t2;
  array mb(100) _temporary_;
  array af(100) _temporary_;
  by gvkey;
  if first.gvkey then counter=0;
  else do;
    counter+1;
    mb(counter)=mb_h;
    af(counter)=afd_h;
    wtdmb_h=0;
    do i=1 to counter;
      wtdmb_h=wtdmb_h+mb(i)*max(0,af(i)/cumafd_h);
    end;
  end;
run;

HTH,

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 6

Re: Macro for computing summation variable

Hi art

 

Thank you very much for your solution to the problem. I ran your code and it seems to work perfectly.

 

Thanks

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 194 views
  • 1 like
  • 3 in conversation