topic Re: Marking all the way back to 1 year ago from each designated date in SAS Programming

Marking all the way back to 1 year ago from each designated date

KS99 — Mon, 09 May 2022 02:36:06 GMT

Hi, greetings!

I appreciate your presence, because you are always available whenever I need a help.

Thank you so much in advance, and the problem which is troubling me is the following:

I have a sort of brokerage houses' announcements data on a daily basis.

House_id	Annc_dates	House_close
1	7/1/1996	0
1	8/21/1996	0
1	9/4/1996	0
1	10/1/1996	0
1	11/1/1996	0
1	12/26/1996	0
1	1/28/1997	0
1	2/24/1997	0
1	3/21/1997	0
1	4/29/1997	0
1	5/15/1997	0
1	8/14/1997	0
1	11/3/1997	1
	….........
5	4/20/2000	0
5	5/12/2000	0
5	6/5/2000	0
5	10/12/2000	0
5	11/9/2000	0
5	12/14/2000	0
5	1/26/2001	0
5	2/27/2001	0
5	3/6/2001	0
5	5/23/2001	0
5	7/31/2001	0
5	9/11/2001	1
6	11/11/1987	0
6	12/3/1987	1

What I want to do, is to highlight one-year time span of observations before a house closes (that is, from the last date of a house in the data). For this, I created a variable "House_close" with 1 for the closing (last) dates, and 0 otherwise.

What I want to obtain is like the below:

House_id	Annc_dates	House_close	Span_closing
1	7/1/1996	0	0
1	8/21/1996	0	0
1	9/4/1996	0	0
1	10/1/1996	0	0
1	11/1/1996	0	1
1	12/26/1996	0	1
1	1/28/1997	0	1
1	2/24/1997	0	1
1	3/21/1997	0	1
1	4/29/1997	0	1
1	5/15/1997	0	1
1	8/14/1997	0	1
1	11/3/1997	1	1
	….........
5	4/20/2000	0	0
5	5/12/2000	0	0
5	6/5/2000	0	0
5	10/12/2000	0	1
5	11/9/2000	0	1
5	12/14/2000	0	1
5	1/26/2001	0	1
5	2/27/2001	0	1
5	3/6/2001	0	1
5	5/23/2001	0	1
5	7/31/2001	0	1
5	9/11/2001	1	1
6	11/11/1987	0	1
6	12/3/1987	1	1

As you see, I created a new variable "Span_closing." For House_id = 1, the closing date is 11/3/1997. So, tracking all the way back to one year ago, the closest date is 11/1/1996. Over that one-year time span, I entered Span_closing = 1, otherwise = 0.

In case of House_id = 6, although it has only two observations, I entered Span_closing = 1 for all its obs based on the indicated principle.

I ask for your wisdom!

Sincerely,

KS -,

Re: Marking all the way back to 1 year ago from each designated date

PeterClemmensen — Mon, 09 May 2022 03:26:28 GMT

Why do you want 11/1/1996 to be included? It is not part of the 1 year span?

Re: Marking all the way back to 1 year ago from each designated date

PeterClemmensen — Mon, 09 May 2022 03:29:51 GMT

data have;
input House_id Annc_dates :mmddyy10. House_close;
format Annc_dates mmddyy10.;
datalines;
1 7/1/1996   0
1 8/21/1996  0
1 9/4/1996   0
1 10/1/1996  0
1 11/1/1996  0
1 12/26/1996 0
1 1/28/1997  0
1 2/24/1997  0
1 3/21/1997  0
1 4/29/1997  0
1 5/15/1997  0
1 8/14/1997  0
1 11/3/1997  1
5 4/20/2000  0
5 5/12/2000  0
5 6/5/2000   0
5 10/12/2000 0
5 11/9/2000  0
5 12/14/2000 0
5 1/26/2001  0
5 2/27/2001  0
5 3/6/2001   0
5 5/23/2001  0
5 7/31/2001  0
5 9/11/2001  1
6 11/11/1987 0
6 12/3/1987  1
;

data want(drop = a);
   do until (last.House_id);
      set have;
      by House_id;
      if House_close = 1 then a = Annc_dates;
   end;
   
   do until (last.House_id);
      set have;
      by House_id;
      Span_closing = (Annc_dates >= intnx('year', a, -1, 's'));
      output;
   end;
run;

Re: Marking all the way back to 1 year ago from each designated date

KS99 — Mon, 09 May 2022 05:28:24 GMT

Dear PeterClemmsen,
Oh, you are absolutely right!
I made a mistake. It should go up to 12/26/1996. To add, the day difference between the first and the last should be closest to 365, but not more than 365.

So, PeterClemmsen, you code below reflects it?

Re: Marking all the way back to 1 year ago from each designated date

PeterClemmensen — Mon, 09 May 2022 05:34:40 GMT

It does.

Result

House_id  Annc_dates  House_close  Span_closing
1         07/01/1996  0            0
1         08/21/1996  0            0
1         09/04/1996  0            0
1         10/01/1996  0            0
1         11/01/1996  0            0
1         12/26/1996  0            1
1         01/28/1997  0            1
1         02/24/1997  0            1
1         03/21/1997  0            1
1         04/29/1997  0            1
1         05/15/1997  0            1
1         08/14/1997  0            1
1         11/03/1997  1            1
5         04/20/2000  0            0
5         05/12/2000  0            0
5         06/05/2000  0            0
5         10/12/2000  0            1
5         11/09/2000  0            1
5         12/14/2000  0            1
5         01/26/2001  0            1
5         02/27/2001  0            1
5         03/06/2001  0            1
5         05/23/2001  0            1
5         07/31/2001  0            1
5         09/11/2001  1            1
6         11/11/1987  0            1
6         12/03/1987  1            1

Re: Marking all the way back to 1 year ago from each designated date

KS99 — Mon, 09 May 2022 15:10:07 GMT

Thank you PeterClemmsen!
Your codes work perfectly!
I wish you a good day!

Sincerely,
KS -,

Re: Marking all the way back to 1 year ago from each designated date

KS99 — Mon, 09 May 2022 23:04:43 GMT

Please, anyone from SAS community provide me a solution, if you are available.

Dear PeterClemmensen,

In relation to my previous task, I again bumped into an obstacle.

Now, I proc sorted my dataset by cusip(=firm id), Analyst_id, Annc_date, and Span_closing.

Span_closing is the final 1-year span before a house closes.

cusip	House_id	Analyst_id	Annc_date	Span_closing
00030710	2143	134474	2/23/2016	0
00030710	2143	134474	3/18/2016	0
00030710	2143	134474	8/5/2016	1
00030710	2143	134474	11/4/2016	1
00030710	2143	134474	2/27/2017	1
00030710	2143	134474	3/1/2017	1
00030710	3945	165909	10/23/2015	1
00030710	3945	165909	2/5/2016	1
00030710	3945	165909	2/24/2016	1
00032Q10	926	194378	8/30/2021	1
00032Q10	926	194378	11/11/2021	1
00036020	371	1112	3/22/1994	0
00036020	371	1112	6/7/1994	0
00036020	371	1112	11/21/1994	0
00036110	32	136	6/29/1989	0
00036110	32	136	1/4/1990	0
00036110	32	136	3/29/1990	1
00036110	32	136	5/10/1990	1
00036110	32	136	8/2/1990	1
00036110	32	136	8/15/1990	1
00036110	32	136	8/30/1990	1
00036110	32	136	10/24/1990	1
00036110	32	136	10/31/1990	1
00036110	32	136	1/10/1991	1
00036110	479	136	5/2/1991	0
00036110	479	136	8/27/1991	0
00036110	479	136	9/12/1991	0
00036110	479	136	10/1/1991	0
00036110	479	136	10/31/1991	0
00036110	479	136	12/30/1991	0
00036110	479	136	4/2/1992	0
00036110	479	136	7/1/1992	0
00036110	479	136	12/21/1992	0
00036110	479	136	1/26/1993	0
00036110	165	278	9/8/1988	0

In this dataset, you can see that Analyst_id= 134474, who was working for the house 2143, stopped working (i.e., disappeared) as soon as the house 2143 closes (Span_closing=1, especially on 3/1/2017).

It is also the case with Analyst_id= 165909 working for the house 3945, and with Analyst_id= 194378 working for the house 926. You can simply leave such cases.

However, Analyst_id= 136, who used to work for the house 32, enters the final year span of the house 32 (Span_closing=1), and after the house closes, he continues to work for another house 479. For such an analyst like him, I'd like to create a new variable "Analyst_persist" that highlights his 1-year span (5/2/1991~4/2/1992) in which he works for a new house.

That is, I'd like to create a dataset like below:

cusip	House_id	Analyst_id	Annc_date	Span_closing	Analyst_persist
00030710	2143	134474	2/23/2016	0	0
00030710	2143	134474	3/18/2016	0	0
00030710	2143	134474	8/5/2016	1	0
00030710	2143	134474	11/4/2016	1	0
00030710	2143	134474	2/27/2017	1	0
00030710	2143	134474	3/1/2017	1	0
00030710	3945	165909	10/23/2015	1	0
00030710	3945	165909	2/5/2016	1	0
00030710	3945	165909	2/24/2016	1	0
00032Q10	926	194378	8/30/2021	1	0
00032Q10	926	194378	11/11/2021	1	0
00036020	371	1112	3/22/1994	0	0
00036020	371	1112	6/7/1994	0	0
00036020	371	1112	11/21/1994	0	0
00036110	32	136	6/29/1989	0	0
00036110	32	136	1/4/1990	0	0
00036110	32	136	3/29/1990	1	0
00036110	32	136	5/10/1990	1	0
00036110	32	136	8/2/1990	1	0
00036110	32	136	8/15/1990	1	0
00036110	32	136	8/30/1990	1	0
00036110	32	136	10/24/1990	1	0
00036110	32	136	10/31/1990	1	0
00036110	32	136	1/10/1991	1	0
00036110	479	136	5/2/1991	0	1
00036110	479	136	8/27/1991	0	1
00036110	479	136	9/12/1991	0	1
00036110	479	136	10/1/1991	0	1
00036110	479	136	10/31/1991	0	1
00036110	479	136	12/30/1991	0	1
00036110	479	136	4/2/1992	0	1
00036110	479	136	7/1/1992	0	0
00036110	479	136	12/21/1992	0	0
00036110	479	136	1/26/1993	0	0
00036110	165	278	9/8/1988	0	0

I tried to make a little modification of your previous codes and achieve this, but, unfortunately, my understanding of the sas function intnx is very limited and I couldn't do it.

I'd really appreciate it, if you give me a hand one more time!

Sincerely,

KS -,