topic Finding mean in Statistical Procedures

Finding mean

Quannguyen — Thu, 11 Feb 2021 02:54:01 GMT

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?

Re: Finding mean

qoit — Thu, 11 Feb 2021 03:34:07 GMT

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

join the datasets first
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;

Re: Finding mean

Quannguyen — Thu, 11 Feb 2021 04:19:33 GMT

3000285MM

Air Methods Corp.

American Securities LLC

11641

03/14/2017

Complete

04/21/2017

Cash

1789

2376

1163

TRANS

OTHER

3000547MM

KCG Holdings, Inc.

Virtu Financial, Inc.

13998

03/15/2017

Complete

07/20/2017

Cash

6261

5794

-0

1142

-0

FIN

3001983MM

Stonegate Bank

Home Bancshares, Inc. (Arkansas)

14671

03/27/2017

Complete

06/30/2002

1.73

07/01/2002

1.83

07/02/2002

1.72

07/03/2002

1.71

07/04/2002

1.71

07/05/2002

1.72

07/06/2002

1.72

07/07/2002

1.72

07/08/2002

1.75

07/09/2002

1.72

07/10/2002

1.74

07/11/2002

1.76

Re: Finding mean

Quannguyen — Thu, 11 Feb 2021 04:21:00 GMT

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

Re: Finding mean

qoit — Thu, 11 Feb 2021 04:28:07 GMT

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

Re: Finding mean

Quannguyen — Thu, 11 Feb 2021 04:33:03 GMT

here are two screenshots of them

Re: Finding mean

qoit — Thu, 11 Feb 2021 05:49:04 GMT

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;

Re: Finding mean

Quannguyen — Thu, 11 Feb 2021 05:57:22 GMT

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?

Re: Finding mean

qoit — Thu, 11 Feb 2021 06:08:43 GMT

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?

Re: Finding mean

Quannguyen — Thu, 11 Feb 2021 06:19:31 GMT

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).

Re: Finding mean

andreas_lds — Thu, 11 Feb 2021 06:21:51 GMT

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.

Re: Finding mean

qoit — Thu, 11 Feb 2021 06:28:43 GMT

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.

Re: Finding mean

Quannguyen — Thu, 11 Feb 2021 06:41:45 GMT

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