calculate by id

[sasphd]

hello, 

I want to this caluation by rank_no and to specify that if I begin a new rank_no  not to do it 

I write this but it seems not work?

data flux1;
set JOINT_Ftna2;
by rank_no;
rtri=((mret))*(lag1(mret))*(lag2(mret))-1;
flux=(new_TNA_LATEST-lag(new_TNA_LATEST)*(1+rtri))/lag(new_TNA_LATEST);
if first.rank_no then flux=.;
run;

I have this data

CALDT	Rank_NO	MRET	new_TNA_LATEST	rtri	flux
1985-03-29	1	1,00935745	39,2
1985-04-30	1	1,00061805	39,2	0,01061174	-0,01061174
1985-05-31	1	1,05514655	39,2	0,06567826	-0,06567826
1985-06-28	1	1,02113917	39,2	0,07811738	-0,07811738
1985-07-31	1	1,00115009	39,2	0,07869063	-0,07869063
1985-08-30	1	0,99478865	39,2	0,01698593	-0,01698593
1985-09-30	1	0,98020955	39,2	-0,02377722	0,02377722
1985-10-31	1	1,05225653	39,2	0,02605675	-0,02605675
1985-11-29	1	1,04958722	39,2	0,08257774	-0,08257774
1985-12-31	1	1,04352557	39,2	0,15250617	-0,15250617
1989-01-31	2	1,02924367	2,792	0,01954959
1989-02-28	2	0,98887854	2,792	-0,00035636	0,00035636
1989-03-31	2	1,01144648	2,792	0,02944717	-0,02944717
1989-04-28	2	1,04709994	2,792	0,04730697	-0,04730697
1989-05-31	2	1,05215827	2,792	0,11432562	-0,11432562
1989-06-30	2	0,9864359	2,792	0,0867711	-0,0867711
1989-07-31	2	1,050077	2,792	0,08986095	-0,08986095
1989-08-31	2	1,04562886	2,792	0,08309757	-0,08309757
1989-09-29	2	0,997994	2,792	0,09578825	-0,09578825

[PaigeMiller]

I want to this caluation by rank_no and to specify that if I begin a new rank_no  not to do it 

I'm not sure what you mean by this. Can you show us the desired output?

I write this but it seems not work?

What about it doesn't work? Show us.

[sasphd]

if first.rank_no then flux=.;

[PaigeMiller]

---

@sasphd wrote:

if first.rank_no then flux=.;

---

Isn't that what you get? Explain. 

[ballardw]

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "<>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "<>" icon or attached as text to show exactly what you have and that we can test code against.

---

@sasphd wrote:

hello, 

I want to this caluation by rank_no and to specify that if I begin a new rank_no  not to do it 

I write this but it seems not work?

data flux1;
set JOINT_Ftna2;
by rank_no;
rtri=((mret))*(lag1(mret))*(lag2(mret))-1;
flux=(new_TNA_LATEST-lag(new_TNA_LATEST)*(1+rtri))/lag(new_TNA_LATEST);
if first.rank_no then flux=.;
run;

I have this data

CALDT	Rank_NO	MRET	new_TNA_LATEST	rtri	flux
1985-03-29	1	1,00935745	39,2
1985-04-30	1	1,00061805	39,2	0,01061174	-0,01061174
1985-05-31	1	1,05514655	39,2	0,06567826	-0,06567826
1985-06-28	1	1,02113917	39,2	0,07811738	-0,07811738
1985-07-31	1	1,00115009	39,2	0,07869063	-0,07869063
1985-08-30	1	0,99478865	39,2	0,01698593	-0,01698593
1985-09-30	1	0,98020955	39,2	-0,02377722	0,02377722
1985-10-31	1	1,05225653	39,2	0,02605675	-0,02605675
1985-11-29	1	1,04958722	39,2	0,08257774	-0,08257774
1985-12-31	1	1,04352557	39,2	0,15250617	-0,15250617
1989-01-31	2	1,02924367	2,792	0,01954959
1989-02-28	2	0,98887854	2,792	-0,00035636	0,00035636
1989-03-31	2	1,01144648	2,792	0,02944717	-0,02944717
1989-04-28	2	1,04709994	2,792	0,04730697	-0,04730697
1989-05-31	2	1,05215827	2,792	0,11432562	-0,11432562
1989-06-30	2	0,9864359	2,792	0,0867711	-0,0867711
1989-07-31	2	1,050077	2,792	0,08986095	-0,08986095
1989-08-31	2	1,04562886	2,792	0,08309757	-0,08309757
1989-09-29	2	0,997994	2,792	0,09578825	-0,09578825

---

[Reeza · Accepted solution]

Define 'seems not work'?

Please show what are you expecting versus what you're getting in the future.

You're setting FLUX to missing at the first of each rank which seems to be happening? Did you want to do the same for RTI as well?

Since you didn't reset that variable, it is crossing ranks as well. 

If that's the case, modify your IF statement to include both variables and use CALL MISSING which allows you to do this for multiple variables at once.

data flux1;
set JOINT_Ftna2;
by rank_no;
rtri=((mret))*(lag1(mret))*(lag2(mret))-1;
flux=(new_TNA_LATEST-lag(new_TNA_LATEST)*(1+rtri))/lag(new_TNA_LATEST);
if first.rank_no then call missing( flux, RTI);
run;

---

@sasphd wrote:

hello, 

I want to this caluation by rank_no and to specify that if I begin a new rank_no  not to do it 

I write this but it seems not work?

data flux1;
set JOINT_Ftna2;
by rank_no;
rtri=((mret))*(lag1(mret))*(lag2(mret))-1;
flux=(new_TNA_LATEST-lag(new_TNA_LATEST)*(1+rtri))/lag(new_TNA_LATEST);
if first.rank_no then flux=.;
run;

I have this data

CALDT	Rank_NO	MRET	new_TNA_LATEST	rtri	flux
1985-03-29	1	1,00935745	39,2
1985-04-30	1	1,00061805	39,2	0,01061174	-0,01061174
1985-05-31	1	1,05514655	39,2	0,06567826	-0,06567826
1985-06-28	1	1,02113917	39,2	0,07811738	-0,07811738
1985-07-31	1	1,00115009	39,2	0,07869063	-0,07869063
1985-08-30	1	0,99478865	39,2	0,01698593	-0,01698593
1985-09-30	1	0,98020955	39,2	-0,02377722	0,02377722
1985-10-31	1	1,05225653	39,2	0,02605675	-0,02605675
1985-11-29	1	1,04958722	39,2	0,08257774	-0,08257774
1985-12-31	1	1,04352557	39,2	0,15250617	-0,15250617
1989-01-31	2	1,02924367	2,792	0,01954959
1989-02-28	2	0,98887854	2,792	-0,00035636	0,00035636
1989-03-31	2	1,01144648	2,792	0,02944717	-0,02944717
1989-04-28	2	1,04709994	2,792	0,04730697	-0,04730697
1989-05-31	2	1,05215827	2,792	0,11432562	-0,11432562
1989-06-30	2	0,9864359	2,792	0,0867711	-0,0867711
1989-07-31	2	1,050077	2,792	0,08986095	-0,08986095
1989-08-31	2	1,04562886	2,792	0,08309757	-0,08309757
1989-09-29	2	0,997994	2,792	0,09578825	-0,09578825

---