topic Re: calculate by id in SAS Procedures

calculate by id

sasphd — Tue, 02 Nov 2021 17:52:32 GMT

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

Re: calculate by id

PaigeMiller — Tue, 02 Nov 2021 18:18:13 GMT

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.

Re: calculate by id

ballardw — Tue, 02 Nov 2021 17:58:50 GMT

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-data-AKA-generate/ta-p/258712 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

Re: calculate by id

Reeza — Tue, 02 Nov 2021 17:59:44 GMT

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

Re: calculate by id

sasphd — Tue, 02 Nov 2021 18:01:45 GMT

if first.rank_no then flux=.;

Re: calculate by id

PaigeMiller — Tue, 02 Nov 2021 18:19:09 GMT

@sasphd wrote:

if first.rank_no then flux=.;

Isn't that what you get? Explain.