Zhongda Tracker

Re: lag one variable in each setting group, using lag function.

Zhongda — Wed, 04 Oct 2023 13:44:46 GMT

Warren, Thank you so much for pointing out this, this helps me alot.

lag one variable in each setting group, using lag function.

Zhongda — Sat, 30 Sep 2023 23:54:38 GMT

Hi All,

here is the dataset:

data have;
input Id$ year income;
cards;
1 2000 33
1 2001 71
1 2002 76
1 2003 56
1 2004 56
2 2000 39
2 2001 74
2 2002 36
2 2003 48
2 2004 46
3 2000 34
3 2001 44
3 2002 69
3 2003 49
3 2004 31
4 2000 48
4 2001 38
4 2002 79
4 2003 48
4 2004 70
;
run;

and here is the target dataset:

id year income want_lag1_income want_lag2_income
1 2000 33 . .
1 2001 71 33 .
1 2002 76 71 33
1 2003 56 76 71
1 2004 56 56 76
2 2000 39 . .
2 2001 74 39 .
2 2002 36 74 39
2 2003 48 36 74
2 2004 46 48 36
3 2000 34 . .
3 2001 44 34 .
3 2002 69 44 34
3 2003 49 69 44
3 2004 31 49 69
4 2000 48 . .
4 2001 38 48 .
4 2002 79 38 48
4 2003 48 79 38
4 2004 70 48 79

Could you please help how to use

lag function to create a one period lag and two period lag

by a setting group, here I mean by id and year

I would like the lag 1 has 1 missing value for each group and lag 2 has 2 missing value.

Thank you so much for the help.

Re: calculate the average for a [+1,+3} and a [-3,-1] window

Zhongda — Tue, 12 Sep 2023 04:35:36 GMT

Hi mkeintz,

Thanks for bringing this issue out.

The code you provide is good but one thing for my working project is that there are different identifier in different years. While I am using proc sort, I can sort the data into group according to each id and year.

then I can apply the ''lag method'' within each group.

I would like to know how can we achieve this in the method you provide.

Here are the sample dataset, and thank you for your time.

my object is to calculate each id a [+1,+3] window and a [-3,-1] window for the average value.

id year value

1 1990 42

1 1991 16

1 1992 25

1 1993 20

1 1994 34

1 1995 44

1 1996 14

1 1997 35

1 1998 42

1 1999 37

2 1990 36

2 1991 21

2 1992 12

2 1993 40

2 1994 32

2 1995 36

2 1996 31

2 1997 43

2 1998 37

2 1999 33

3 1990 36

3 1991 33

3 1992 30

3 1993 20

3 1994 15

3 1995 34

3 1996 36

3 1997 20

3 1998 37

3 1999 40

Re: calculate the average for a [+1,+3} and a [-3,-1] window

Zhongda — Tue, 12 Sep 2023 03:24:29 GMT

Thank you!

Re: calculate the average for a [+1,+3} and a [-3,-1] window

Zhongda — Mon, 11 Sep 2023 17:04:12 GMT

Hi Ballardw,

Thank you so much for your solution and it perfectly works for a [+1,+3] window. I would like to ask what if I want to calculate another average for window [-3,-1] for each observation? This means, for the first three obs in the sample, this is NA and it should start from the 4th obs and the value will be calculated as (14+6+3)/3

thanks

calculate the average for a [+1,+3} and a [-3,-1] window

Zhongda — Mon, 11 Sep 2023 16:21:44 GMT

Hi all,

I am working for question similar to moving average.

for the first obs #1 value 14, I would like to get the number for average for next three values as (6+3+5)/3

which means a [+1,+3} window for the average.

I looked some of the proc expand command but I did not get a solution.

Thanks for the help.

Zhongda

here are the data

# value average for next three values
1 14 4.666666667 =AVERAGE(B3:B5)
2 6 7.333333333 =AVERAGE(B4:B6)
3 3 7.666666667 =AVERAGE(B5:B7)
4 5 11.66666667 =AVERAGE(B6:B8)
5 14 9 =AVERAGE(B7:B9)
6 4 11.33333333 =AVERAGE(B8:B10)
7 17 10.33333333 =AVERAGE(B9:B11)
8 6
9 11
10 14

rebalance portfolio using June of each year

Zhongda — Wed, 09 Nov 2022 04:34:04 GMT

Hi ALL,

Again, thank you so much for getting the help so far.

Currently, I am working on rebalance the portfolio, and make it ten groups. based on the monthly return of the stocks. Before I know how to rebalance it each month, but at this moment, I need to rebalance it using the market cap of each June as the weights for the coming one year. I do not know how to deal with it.

there are two difficulties here,

First is how to deal with the missing value, for example,

in June 1986 we have 10 stocks and according to the market cap you rank them from 1 to 10. but for July 1986, you may have some refill of the stocks, either the original 10 stocks could delist some or there are some new adding stock in July.

Second is how to use this weight for each June to group the data for next coming 12 month.

I upload a very simple dataset which the first problem is excluded, but I still would be more than happy if you can include the code that solve this first difficulty as well.

here is the dataset, please let me know if you have any suggestions, thanks a lot.

MTHCALDT	PERMNO	MTHCAP	MTHRET	year	month
1/31/86	10000	16100	0.707317	1986	1
1/31/86	10001	6033.125	0.065217	1986	1
1/31/86	10002	13659.38	-0.01064	1986	1
1/31/86	10003	41800	0.189189	1986	1
2/28/86	10000	11960	-0.25714	1986	2
2/28/86	10001	6156.25	0.020408	1986	2
2/28/86	10002	15568.75	0.139785	1986	2
2/28/86	10003	42275	0.011364	1986	2
3/31/86	10000	16330	0.365385	1986	3
3/31/86	10001	6217.813	0.025352	1986	3
3/31/86	10002	16670.31	0.070755	1986	3
3/31/86	10003	41800	-0.01124	1986	3
4/30/86	10000	15172	-0.09859	1986	4
4/30/86	10001	6279.375	0.009901	1986	4
4/30/86	10002	17551.56	0.052863	1986	4
4/30/86	10003	39187.5	-0.0625	1986	4
5/30/86	10000	11793.86	-0.22266	1986	5
5/30/86	10001	6217.813	-0.0098	1986	5
5/30/86	10002	17184.38	-0.02092	1986	5
5/30/86	10003	44792.5	-0.00606	1986	5
6/30/86	10000	11734.59	-0.00503	1986	6
6/30/86	10001	6033.125	-0.01324	1986	6
6/30/86	10002	14907.81	-0.13248	1986	6
6/30/86	10003	47523.75	0.060976	1986	6
7/31/86	10000	10786.34	-0.08081	1986	7
7/31/86	10001	5971.563	-0.0102	1986	7
7/31/86	10002	15642.19	0.049261	1986	7
7/31/86	10003	42061.25	-0.11494	1986	7
8/29/86	10000	4148.594	-0.61538	1986	8
8/29/86	10001	6402.5	0.072165	1986	8
8/29/86	10002	15642.19	0	1986	8
8/29/86	10003	42607.5	0.012987	1986	8
9/30/86	10000	3911.531	-0.05714	1986	9
9/30/86	10001	6317.625	-0.00397	1986	9
9/30/86	10002	16743.75	0.070423	1986	9
9/30/86	10003	40149.38	-0.05769	1986	9
10/31/86	10000	3002.344	-0.24242	1986	10
10/31/86	10001	6565.375	0.039216	1986	10
10/31/86	10002	15715.63	-0.0614	1986	10
10/31/86	10003	45338.75	0.129252	1986	10
11/28/86	10000	3182.484	0.06	1986	11
11/28/86	10001	6937	0.056604	1986	11
11/28/86	10002	15715.63	0	1986	11
11/28/86	10003	44246.25	-0.0241	1986	11
12/31/86	10000	1981.547	-0.37736	1986	12
12/31/86	10001	6937	0.016154	1986	12
12/31/86	10002	14540.63	-0.07477	1986	12
12/31/86	10003	42061.25	-0.04938	1986	12
1/30/87	10000	1581.531	-0.21212	1987	1
1/30/87	10001	6689.25	-0.03571	1987	1
1/30/87	10002	15935.94	0.09596	1987	1
1/30/87	10003	47523.75	0.12987	1987	1
2/27/87	10000	1581.531	0	1987	2
2/27/87	10001	6193.75	-0.07407	1987	2
2/27/87	10002	15715.63	-0.01382	1987	2
2/27/87	10003	48070	0.011494	1987	2
3/31/87	10000	973.25	-0.38462	1987	3
3/31/87	10001	6317.625	0.037486	1987	3
3/31/87	10002	15128.13	-0.03738	1987	3
3/31/87	10003	48070	0	1987	3
4/30/87	10000	912.4219	-0.0625	1987	4
4/30/87	10001	6069.875	-0.03922	1987	4
4/30/87	10002	15275	0.009709	1987	4
4/30/87	10003	45611.88	-0.05114	1987	4
5/29/87	10000	851.5938	-0.06667	1987	5
5/29/87	10001	5636.313	-0.07143	1987	5
5/29/87	10002	15862.5	0.038462	1987	5
5/29/87	10003	39985.75	-0.12575	1987	5
6/30/87	10000	851.5938	0	1987	6
6/30/87	10001	5822.125	0.052687	1987	6
6/30/87	10002	15862.5	0	1987	6
6/30/87	10003	38890.25	-0.0274	1987	6
7/31/87	10001	5946	0.021277	1987	7
7/31/87	10002	16450	0.037037	1987	7
7/31/87	10003	36973.13	-0.0493	1987	7
8/31/87	10001	6441.5	0.083333	1987	8
8/31/87	10002	15862.5	-0.03571	1987	8
8/31/87	10003	39164.13	0.065144	1987	8
9/30/87	10001	6200	-0.02163	1987	9
9/30/87	10002	14687.5	-0.07407	1987	9
9/30/87	10003	37520.88	-0.04196	1987	9
10/30/87	10001	6324	0.02	1987	10
10/30/87	10002	11750	-0.2	1987	10
10/30/87	10003	29030.75	-0.22628	1987	10
11/30/87	10001	6138	-0.02941	1987	11
11/30/87	10002	11896.88	0.0125	1987	11
11/30/87	10003	31813	0.113208	1987	11
12/31/87	10001	5828	-0.03354	1987	12
12/31/87	10002	11750	-0.01235	1987	12
12/31/87	10003	30716	-0.03448	1987	12

Re: cumulative count under condtion

Zhongda — Wed, 09 Nov 2022 00:20:11 GMT

Hi,
I found that this will work that is add one more line for if clause
as
data event_program_more;
set event_program;
by permno program_code ;
retain counter_overall;
if first.program_code then counter_overall=1;
else counter_overall = counter_overall+1;
if counter_overall > program_code then counter_overall=1;

run;

cumulative count under condtion

Zhongda — Tue, 08 Nov 2022 06:10:29 GMT

Hi ALL,

I ma trying to count the varible in the group call counter_overall, but my code does not work for this special case below:

here is the dataset:

PERMNO	EVTDATE	RET	DATE	days_to_previous_p	days_to_next	first_event	inter_event	program_code	counter_overall	correct_overall
10028	8/17/99	0.015625	8/17/99		199	1	0	2	1	1
10028	3/3/00	-0.03448	3/3/00	199	2324	0	1	2	2	2
10028	7/14/06	0	7/14/06	2324	300	1	0	2	3	1
10028	5/10/07	0.004	5/10/07	300		0	1	2	4	2

the counter_overall is the target I want but the numbers are wrong and the next column correct_overall is the right numbers

basically, if you take a look at the varible : program_code, for this case, it is 2,2,2,2. 2 means two event in a program and then with this program, the program_code is 2,2 and

2,2,2,2 here means, for permno 10028, there are two successive "two event program", my target is to number the sequence of the event in these two events groups.

so there is an invisible condition that is the counter_overall can not access the number of program code. Sorry I know it is quite confused , but let me explain it in another case:

suppose there is another stock permno 10000, and it has two successive three-event programs.

so the program code for this 6 obs is 3,3,3 , 3,3,3

and I would like to number the sequence as 1,2,3 1,2,3 instead of 1,2,3,4,5,6.

here is my code but it does not work FYI:

proc sort data = event_program;
by permno program_code;
run;

data event_program_more;
set event_program;
by permno program_code ;
retain counter_overall;
if first.program_code then counter_overall=1;
else counter_overall = counter_overall+1;

run;

Again, thanks a lot for the help.

Zhongda

lag variable with by function of two parameters

Zhongda — Tue, 08 Nov 2022 02:52:22 GMT

Dear All,

I was trying to create a new variable ， called lag_ar.

here is my original dataset:

PERMNO	EVTDATE	EVTTIME	cumulative_ar	program_code	counter_overall
10001	8/1/11	5	0.933284	2	1
10001	4/2/12	5	0.98016	2	2
10002	7/6/98	5	1.14444	2	1
10002	10/27/98	5	0.947167	2	2
10020	4/6/87	5	1.003912	3	1
10020	10/21/87	5	0.898007	3	2
10020	12/11/87	5	1.032888	3	3
10020	4/17/90	5	1.023325	2	1
10020	4/24/90	5	1.063576	2	2

my code is

proc sort data = datasetname;
by permno evtdate program_code;
run;
data within_lag;
set within;
by permno evtdate program_code;
cum_ar_lag1 = ifn(not(first.permno),lag(cumulative_ar),.);

run;

but with my code, the result is not right;

take permno 10020 for example,

my code gives the result as below:

10020	4/6/87	5	1.003912	3	1
10020	10/21/87	5	0.898007	3	2	1.003912
10020	12/11/87	5	1.032888	3	3	0.898007
10020	4/17/90	5	1.023325	2	1	1.032888
10020	4/24/90	5	1.063576	2	2	1.023325

however, for the 4th obs, the lag_ar should be ".", but it still grab the value from the 3rd observation. however, they are not in the same program code, which it should not grab the value and assign it there.

the right result I expect is as below:

the lag is supposed to do within same permno and same program_code.

PERMNO	EVTDATE	EVTTIME	cumulative_ar	program_code	counter_overall	lag_ar
10020	4/6/87	5	1.003912	3	1
10020	10/21/87	5	0.898007	3	2	1.003912
10020	12/11/87	5	1.032888	3	3	0.898007
10020	4/17/90	5	1.023325	2	1
10020	4/24/90	5	1.063576	2	2	1.023325

I know I can fix my wrong result by assign a missing value to all obs that counter_overall is 1, but I really want to figure out why my

by statement does not work.

Thank you so much again.

Zhongda

Re: assign value for a group under condition

Zhongda — Tue, 01 Nov 2022 02:38:59 GMT

Hi Peter,

Thank you so much for your kind help. This code does work very well, but could you please explain the syntax so that I can have a better understanding for each line.

Really appreciate your help.

Zhongda

assign value for a group under condition

Zhongda — Mon, 31 Oct 2022 03:54:16 GMT

Hi All,

I am new to sas and I was trying to achieve such thing:

here is the dataset:

PERMNO EVTDATE first_event inter_event

10001 8/1/11 1 0

10001 4/2/12 0 1

10002 7/6/98 1 0

10002 10/27/98 0 1

10011 11/13/95 1 0

10011 5/28/96 0 1

10011 5/29/96 0 1

10016 12/9/99 1 0

10016 5/3/00 0 1

10020 4/6/87 1 0

10020 10/21/87 0 1

10020 12/11/87 0 1

10020 4/17/90 1 0

10020 4/24/90 0 1

10028 8/17/99 1 0

10028 3/3/00 0 1

10028 7/14/06 1 0

10028 5/10/07 0 1

I would like to create another column call program and the column will state as:

PERMNO EVTDATE first_event inter_event define a program, and numbers of event in the group

10001 8/1/11 1 0 2

10001 4/2/12 0 1 2

10002 7/6/98 1 0 2

10002 10/27/98 0 1 2

10011 11/13/95 1 0 3

10011 5/28/96 0 1 3

10011 5/29/96 0 1 3

10016 12/9/99 1 0 2

10016 5/3/00 0 1 2

10020 4/6/87 1 0 3

10020 10/21/87 0 1 3

10020 12/11/87 0 1 3

10020 4/17/90 1 0 2

10020 4/24/90 0 1 2

10028 8/17/99 1 0 2

10028 3/3/00 0 1 2

10028 7/14/06 1 0 2

10028 5/10/07 0 1 2

let say for permno 10001, there are two obs and they form a program and the program has two event in a row, so it will be definded as 2 for each of the two obs.

Thank you so much for your help!

Appreciated,

Zhongda