topic Re: I added more information for my earlier question-create a new variable (balance) in SAS Procedures

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

Anna_Guo — Wed, 19 Dec 2012 19:15:51 GMT

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

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

Haikuo — Wed, 19 Dec 2012 20:06:55 GMT

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

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

Anna_Guo — Wed, 19 Dec 2012 21:18:50 GMT

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

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

Ksharp — Thu, 20 Dec 2012 01:30:38 GMT

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

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

Anna_Guo — Thu, 20 Dec 2012 03:36:51 GMT

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.

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

Haikuo — Thu, 20 Dec 2012 04:15:58 GMT

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

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

Anna_Guo — Thu, 20 Dec 2012 04:20:58 GMT

Hai Kuo, Thank you. It worked.

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

Haikuo — Thu, 20 Dec 2012 04:24:30 GMT

: 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

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

Ksharp — Thu, 20 Dec 2012 04:45:49 GMT

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.