BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mmh
Obsidian | Level 7 mmh
Obsidian | Level 7

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
mmh
Obsidian | Level 7 mmh
Obsidian | Level 7
Thank you so much!
I really appreciate your support 🙂
mickbiden2
Calcite | Level 5

Thank you so much..  

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 959 views
  • 1 like
  • 3 in conversation