Finding mean

[Quannguyen]

Hi. I’m very new to SAS and want to ask a question.
I have 2 data set, 1 has the announcement date for the M&A deal, second has the date and federal rate. I need to find the mean and standard deviation of the rate with in 125 days prior to the announcement date.
Can you guys help me with this?

[qoit]

Hi, are you able to post the first few observations of the datasets? It seems like you need to

1. join the datasets first
2. Use PROC MEANS to calculate the Standard Deviation and Mean as 

PROC MEANS DATA=my_dataset (WHERE = (announcement_date >= INTCK('DAY',announcement_date,-125,'s')) STD MEAN;

VAR rate;

RUN;

[Quannguyen]

3000285MM

Air Methods Corp.

American Securities LLC

11641

03/14/2017

Complete

.

04/21/2017

1

Cash

0

1

0

0

0

0

1789

2376

0

1

0

.

0

1163

0

0

12

12

TRANS

OTHER

2

3000547MM

KCG Holdings, Inc.

Virtu Financial, Inc.

13998

03/15/2017

Complete

.

07/20/2017

0

Cash

0

1

0

0

0

.

6261

5794

-0

1

0

.

0

1142

0

-0

11

11

FIN

FIN

3

3001983MM

Stonegate Bank

Home Bancshares, Inc. (Arkansas)

14671

03/27/2017

Complete

1

06/30/2002

1.73

2

07/01/2002

1.83

3

07/02/2002

1.72

4

07/03/2002

1.71

5

07/04/2002

1.71

6

07/05/2002

1.72

7

07/06/2002

1.72

8

07/07/2002

1.72

9

07/08/2002

1.75

10

07/09/2002

1.72

11

07/10/2002

1.74

12

07/11/2002

1.76

[Quannguyen]

Hi. Can you see the table? I wonder if you can show me how to join two tables as well

[qoit]

I am unable to tell which dataset is which? Are you able to attach a few rows in a tabular format of both datasets?

[Quannguyen]

here are two screenshots of them

[qoit]

It is assumed that both the datasets can be joined using "Announcement Date" in dataset1 and "Date" in dataset2. I could not find an ID Variable in dataset2:

proc sql;
	create table dsn as
		select data1.deal_id,data1.announcement_date,data2.date,data2.rate
			from dataset1 as data1 
				inner join /* Assuming we only need common observations i.e. which exists in both datasets*/
	dataset2 as data2
	on data1.announcement_date = data2.date;
quit;

PROC MEANS DATA=dsn(WHERE=(nannouncement_date >= INTCK('DAY',announcement_date,-125,'s'))) STD MEAN;
	VAR rate;
RUN;

[Quannguyen]

So in the announcement_date dataset, there are 640 observations while in the rate dataset there are 40,000+ observations. So I don't know how to join them. Basically, I need to find the average rate for 640 deals which range 125 days before the announcement date. I don't know if it makes sense?

[qoit]

Would you know if the Deal_ID variable (column) exists in the dataset2 (rate dataset)? We need a common unique variable to join both datasets.

In the rate dataset, how do we know which rate belongs to which Deal_id?

[Quannguyen]

No. I don’t. The second dataset only has date and federal rates. So the rate of the announcement deal = mean(federal rate 125 days prior to the announcement date).

[qoit]

In order to get the federal rate for a particular announcement date, we need a common variable to join both datasets. Apologies, either you need to attach 100 rows of both datasets in here for others to assess or gain further clarity from whoever provided you with the data.

[Quannguyen]

Yeah. The date is the only thing that can use to join 2 datasets. So basically the first dataset has deal_id and announcement_date and the second dataset has date and federal date.
The rate for the deal in dataset 1 = the average rate( from dataset2) of 60 days prior to the announcement date. For example: company A has the announcement date 4/1/2019. So the rate for company A = mean of the federal rate from 1/1-4/1/2019

[andreas_lds]

Sorry, but screenshots are even more useless than tables. Both don't show relevant information like variable types, length etc. So please take your time and post data in usable form. Have a look at How to convert datasets to data steps if you don't know what to do.