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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.