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!
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;
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;
Thank you so much..
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.