I added more information for my earlier question-create a new variable (balance)

[Anna_Guo]

I attached a new file which includes two companies ( my actual file contains thousands more), and I need to create  new balances ( last column-FV_new), formula included, by company ID.

Please help.

I just joined  this community yesterday, and I should have joined this earlier. Thanks a lot to anyone who helped me or replied my posting. /Anna

[Haikuo]

I maybe mistaking, but if you only doing the computing within the same row, then 1) you don't need retain, like you did in your last question 2) company_id is irrelevant.

data have;

input Company_ID Year FV_org SF Called RO FV_new;

cards;

1 1990 1000 0 0 0 1000

1 1991 1000 -20 0 0 980

1 1992 1000 0 0 0 1000

1 1993 1000 -20 0 0 980

1 1994 1000 0 0 0 1000

1 1995 1000 -20 -50 0 930

1 1996 1000 -20 0 500 1480

1 1997 1000 0 . 0 1000

1 1998 1000 -20 -50 0 930

1 1999 1000 0 0 0 1000

1 2000 1000 -20 0 0 980

1 2001 1000 0 -50 0 950

1 2002 1000 -20 0 0 980

1 2003 1000 0 -100 0 900

1 2004 1000 -20 0 0 980

2 2000 600 0 0 0 600

2 2001 600 0 0 0 600

2 2002 600 0 60 0 660

2 2003 600 0 0 0 600

2 2004 600 0 0 600 1200

2 2005 600 0 0 0 600

;

data want;

  set have;

  new_var=sum(of FV_org--RO);

run;

New_var should be identical to your FV-new.

Haikuo

[Anna_Guo]

Hai Kuo,

Thanks. But, I need the lagged newFV, for example, the third line of your output should be 980, not 1000.Oops I made mistakes again. ( I was in a hurry). 1993( line 4) should be 960. !994( line5) is 960, and 1995( line 6) is 890.

Please help. Thanks./Anna

[Ksharp · Accepted solution]

data have;
input Company_ID Year FV_org SF Called RO FV_new;
cards;
1 1990 1000 0 0 0 1000
1 1991 1000 -20 0 0 980
1 1992 1000 0 0 0 1000
1 1993 1000 -20 0 0 980
1 1994 1000 0 0 0 1000
1 1995 1000 -20 -50 0 930
1 1996 1000 -20 0 500 1480
1 1997 1000 0 . 0 1000
1 1998 1000 -20 -50 0 930
1 1999 1000 0 0 0 1000
1 2000 1000 -20 0 0 980
1 2001 1000 0 -50 0 950
1 2002 1000 -20 0 0 980
1 2003 1000 0 -100 0 900
1 2004 1000 -20 0 0 980
2 2000 600 0 0 0 600
2 2001 600 0 0 0 600
2 2002 600 0 60 0 660
2 2003 600 0 0 0 600
2 2004 600 0 0 600 1200
2 2005 600 0 0 0 600
;
run;

 

data want;
  set have;
  by Company_ID;
  if first.Company_ID then new_var=FV_org;
  new_var+(SF+Called ) ;
run;

 
/*New_var should be identical to your FV-new.*/

Ksharp

[Anna_Guo]

I tried this, but why did new_var show red? I used my new variable name.  SAS didn't run, log tells me invalid . ( amount2 shows red). Below is my code:

data m6_2_4;

set m6_2_3;

  by issue_id;

   if first.issue_id

    then amount2= amount;

      amount2-called-sfpaid+ro_amount;

run;

I also tried adding "retain amount2" after date m6_2_4, still not work.

[Haikuo]

so It seems to me that "called, sfpaid" are not in the form of negative, you just want them to be subtracted. if that is the case:

data m6_2_4;

set m6_2_3;

  by issue_id;

   if first.issue_id

    then amount2= amount;

amount2+(-called)+(-sfpaid)+ro_amount;

run;

Haikuo

[Anna_Guo]

Hai Kuo, Thank you. It worked.

[Haikuo]

: What kind of editor software you are using? It amazes me that there is no additional empty line between your code when I copy them back to SAS program editor.

Haikuo

[Ksharp]

No magic tool there. Just click the HTML commandLinke at the top right side.

and use <pre> data class; set sashelp.class;run; </pre>  . That is all.