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
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
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
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
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
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.
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
Hai Kuo, Thank you. It worked.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.