Creating a variable on the basis of being present in a month or not

[mmh]

Dear altruist,
I have the following sas dataset:

data have;
input Year_Month$ Month$ Year$ Company_ID$ Profit Announcement: yymmdd10.;
format Announcement yymmdd10.;
cards;
200101 01 2001 1001 0.00256 .
200101 01 2001 1002 0.00256 2001-01-25
200101 01 2001 1003 0.00256 .
200101 01 2001 1004 0.00256 2001-01-26
200101 01 2001 1005 0.00256 .
200102 02 2001 1001 0.00214 .
200102 02 2001 1002 0.00214 .
200102 02 2001 1003 0.00214 2001-02-12
200102 02 2001 1003 0.00214 2001-02-13
200102 02 2001 1004 0.00214 .
200102 02 2001 1005 0.00214 .
200102 02 2001 1006 0.00214 .
200103 03 2001 1001 0.33652 .
200103 03 2001 1002 0.33652 2001-03-31
200103 03 2001 1003 0.33652 .
200103 03 2001 1004 0.33652 2001-03-16
200103 03 2001 1005 0.33652 .
200201 01 2002 1001 0.01402 .
200201 01 2002 1002 0.01402 .
200201 01 2002 1003 0.01402 2002-01-25
200201 01 2002 1004 0.01402 2002-01-25
200201 01 2002 1005 0.01402 .
200201 01 2002 1006 0.01402 .
200202 02 2002 1001 0.10201 .
200202 02 2002 1002 0.10201 2003-01-25
200202 02 2002 1003 0.10201 2003-01-26
200202 02 2002 1004 0.10201 .
200202 02 2002 1005 0.10201 .
run;

As you can see, in every month of a year, certain companies make Announcements, while others do not.
On the basis of the variable Year_Month, I want to create a new variable Ann_in_month which will be equal to 1 if a company makes an announcement in that particular month, and 0 otherwise.

Thus, for every month of a given year, this entire process would be repeated.

Essentially, I am expecting the following output:

Year_Month	Month	Year	Company_ID	Profit	Announcement	Ann_in_month
200101	01	2001	1001	0.00256	.	0
200101	01	2001	1002	0.00256	20010125	1
200101	01	2001	1003	0.00256	.	0
200101	01	2001	1004	0.00256	20010126	1
200101	01	2001	1005	0.00256	.	0
200102	02	2001	1001	0.00214	.	0
200102	02	2001	1002	0.00214	.	0
200102	02	2001	1003	0.00214	20010212	1
200102	02	2001	1003	0.00214	20010213	1
200102	02	2001	1004	0.00214	.	0
200102	02	2001	1005	0.00214	.	0
200102	02	2001	1006	0.00214	.	0
200103	03	2001	1001	0.33652	.	0
200103	03	2001	1002	0.33652	20010331	1
200103	03	2001	1003	0.33652	.	0
200103	03	2001	1004	0.33652	20010316	1
200103	03	2001	1005	0.33652	.	0
200201	01	2002	1001	0.01402	.	0
200201	01	2002	1002	0.01402	.	0
200201	01	2002	1003	0.01402	20020125	1
200201	01	2002	1004	0.01402	20020125	1
200201	01	2002	1005	0.01402	.	0
200201	01	2002	1006	0.01402	.	0
200202	02	2002	1001	0.10201	.	0
200202	02	2002	1002	0.10201	20030125	1
200202	02	2002	1003	0.10201	20030126	1
200202	02	2002	1004	0.10201	.	0
200202	02	2002	1005	0.10201	.	0

I thank you for your kind support!

[PaigeMiller · Accepted solution]

PROC FREQ will do this easily

proc freq data=have(where=(not missing(announcement)));
	tables year_month*company_id/noprint out=_count_;
run;
data want;
    merge have _count_;
    by year_month company_id;
    ann_in_month=(count>0);
    drop percent count;
run;

[mmh]

Thank you so much!
I really appreciate your support 🙂

[mickbiden2]

Thank you so much..