Dear altruists,  I have the following SAS datasets: data data1; input Company_ID$ Fiscal_Year Fiscal_Quarter Announcement : yymmdd10.; format Announcement yymmdd10.; cards; 1001 2001 2 2001-08-31 1001 2001 3 2001-11-30 2002 2001 3 2001-07-31 2002 2001 4 2001-10-31 2002 2002 1 2002-01-31 3003 2001 4 2001-10-31 3003 2002 1 2002-01-31 4004 2004 4 2004-12-31 4004 2005 1 2005-04-02 5005 2001 1 2001-01-31 ; run; data data2; input Company_ID$ Company_Name$ Year_End_Month$; cards; 2002 Google December 3003 Amazon June 5005 Apple March ; run; I want to keep only those observations in data1 that matches data2. However, when matching the observations, I also want to include the additional columns that are in data2. Essentially, I am looking for the following output: Company_ID Fiscal_Year Fiscal_Quarter Announcement Company_Name Year_End_Month 2002 2001 3 2001-07-31 Google December 2002 2001 4 2001-10-31 Google December 2002 2002 1 2002-01-31 Google December 3003 2001 4 2001-10-31 Amazon June 3003 2002 1 2002-01-31 Amazon June 5005 2001 1 2001-01-31 Apple March   I have tried the following code, but it does not seem to work: proc sort data=work.data1; by Company_ID; run;   proc sort data=work.data2; by Company_ID; run;   data work.filtered_data; merge work.data1 (in=a) work.data2 (in=b); by Company_ID; if b and not a; run;   (Edit: changed the code a bit, but still does not show any result) Requesting your kind support 🙂 ... View more

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!     ... View more

Dear altruist, I have the following SAS dataset: data have; input Company_ID$ Fiscal_Year Fiscal_Quarter Announcement : yymmdd10.; format Announcement yymmdd10.; cards; 1001 2001 2 2001-08-31 1001 2001 3 2001-11-30 1001 2001 4 2002-02-28 1001 2002 1 2002-05-31 1001 2002 2 2002-08-31 1001 2002 3 2002-11-30 1001 2002 4 2003-02-28 1001 2003 1 2003-05-31 1001 2003 2 2003-08-31 1001 2003 3 2003-11-30 1001 2003 4 2004-02-29 1001 2004 1 2004-05-31 1001 2004 2 2004-08-31 2002 2001 3 2001-07-31 2002 2001 4 2001-10-31 2002 2002 1 2002-01-31 2002 2002 2 2002-04-30 2002 2002 3 2002-07-31 2002 2002 4 2002-10-31 2002 2003 1 2003-01-01 2002 2003 2 2003-04-30 2002 2003 3 2003-07-31 2002 2003 4 2003-10-31 2002 2004 1 2004-01-31 3003 2001 4 2001-10-31 3003 2002 1 2002-01-31 4004 2004 4 2004-12-31 4004 2005 1 2005-04-02 5005 2001 1 2001-01-31 ; run; I want to know what is the total number of observations per Fiscal_Quarter based on the the variable Announcement_Date which is Before and On-or-after 01JAN2003. In the output file, every company will have the all the four fiscal quarters calculated, even if a company only has one observation (Please see Company_ID 5005). Thus, the Fiscal_Quarters 1-4 is displayed for all companies. Essentially, I am expecting the following output: Company_ID Fiscal_Quarter Before_01JAN2003 On_or_after_01JAN2003 1001 1 1 2 1001 2 2 2 1001 3 2 1 1001 4 1 2 2002 1 1 2 2002 2 1 1 2002 3 2 1 2002 4 2 1 3003 1 1 0 3003 2 0 0 3003 3 0 0 3003 4 1 0 4004 1 0 1 4004 2 0 0 4004 3 0 0 4004 4 0 1 5005 1 1 0 5005 2 0 0 5005 3 0 0 5005 4 0 0 I thank you in advance for your kind support! ... View more

Hello, I am a new SAS user. I want to create a new column showing the non-missing value. However, when all the column values are missing, the output column will show a missing value too. I have the following dataset: data have; input Company_ID $4. Date1 Date2 Date3 Date4; informat Date1 Date2 Date3 Date4 date9.; format Date1 Date2 Date3 Date4 date9.; cards; 1210 31mar2009 . . . 1210 . 30jun2009 . . 1210 . . 30sep2009 . 1210 . . . . 1210 . . . 31mar2009 ; run; I am expecting the following output: Company_ID Date1 Date2 Date3 Date4 Date_New 1210 31-Mar-09 . . . 31-Mar-09 1210 . 30-Jun-09 . . 30-Jun-09 1210 . . 30-Sep-09 . 30-Sep-09 1210 . . . . . 1210 . . . 31-Mar-09 31-Mar-09 Thank you in advance for your kind support! ... View more