Calculate the mean in the quarter before the event date

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Calculate the mean in the quarter before the event date

Hi. I am having trouble figuring out the correct SAS Code. I have to calculate the average institutional ownership in a quarter before the event date. The event itself is the adoption of a poison pill by a company. My data spans from 1983 to 2010. This is the code I have for the year 2000. Basically I don't know how to tell SAS to calculate the institutional ownership for a certain company ( cusip) a quarter before the adoption date of poison pill by that particular company.

PROC IMPORT OUT= WORK.INSTOWN2000Q1

            DATAFILE= "C:\Users\a_volko\Desktop\46_q1_2000.csv"

            DBMS=CSV REPLACE;

GETNAMES=YES;

     DATAROW=2;

RUN;

PROC IMPORT OUT= WORK.INSTOWN2000Q2

            DATAFILE= "C:\Users\a_volko\Desktop\45_q2_2000.csv"

            DBMS=CSV REPLACE;

GETNAMES=YES;

     DATAROW=2;

RUN;

PROC IMPORT OUT= WORK.INSTOWN2000Q4

            DATAFILE= "C:\Users\a_volko\Desktop\43_q4_2000.csv"

            DBMS=CSV REPLACE;

GETNAMES=YES;

     DATAROW=2;

RUN;

PROC IMPORT OUT= WORK.INSTOWN2000Q3

            DATAFILE= "C:\Users\a_volko\Desktop\44_q3_2000.csv"

            DBMS=CSV REPLACE;

GETNAMES=YES;

     DATAROW=2;

RUN;

data instown2000;

set WORK.INSTOWN2000Q1 WORK.INSTOWN2000Q2 WORK.INSTOWN2000Q3 WORK.INSTOWN2000Q4;

shrout3=shrout1*1000000;

instown=shares/shrout3;

if shrout1=. then delete;

if shrout3=0 then delete;

if stkcdesc= 'COM'or stkcdesc='CMA' or stkcdesc='CMB' or stkcdesc='CMC' or stkcdesc=''  ;

run;

proc sort data=instown2000; by cusip ;run;

data adoption;

infile 'C:\Users\Anastassia\Desktop/cusip1983(new22).txt' firstobs=2 dsd truncover;

input adoptiondate cusip;

proc sort data=adoption; by cusip; run;


Accepted Solutions
Solution
‎02-22-2013 12:58 PM
Respected Advisor
Posts: 4,659

Re: Calculate the mean in the quarter before the event date

In your earlier post, cusip in adoption dataset didn't have leading zeros. Try this for your join condition instead, as a test :

input(a.cusip, 8.)=input(i.cusip, 8.)

PG

PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,659

Re: Calculate the mean in the quarter before the event date

I don't know if you want to average or sum ownership, just change the function, if needed. Assuming dataset instown2000 contains a date called ownDate :

proc sql;

create table qtrown as

select

     a.cusip,

     a.adoptionDate,

     mean(i.instown) as  qtrinstown /* Change mean to sum, if needed */

from

     adoption as a left join

     instown2000 as i

on

     a.cusip = i.cusip and

     intnx("QTR",a.adoptionDate,-1) = intnx("QTR",i.ownDate,0)

group by

     a.cusip,

     a.adoptionDate;

quit;

 

PG

PG
Contributor
Posts: 20

Re: Calculate the mean in the quarter before the event date

PG Thank you for a fast reply. I tried running the data but the mean inst.ownership values I get are too high.  I am new to SAS, could you explain me how to read the code : intnx("QTR",a.adoptionDate,-1) = intnx("QTR",i.ownDate,0).

In case I haven't explained well I need the institutional ownership only in the quarter before the adoption event, not in all previous years stopping at a quarter before the adoption.

Respected Advisor
Posts: 4,659

Re: Calculate the mean in the quarter before the event date

intnx("QTR",a.adoptionDate,-1)  returns the date of the first day of the first quarter before adoptionDate and intnx("QTR",i.ownDate,0) returns the date of the first day of the quarter containing ownDate. Could you post some small sample datasets? - PG

PG
Contributor
Posts: 20

Re: Calculate the mean in the quarter before the event date

Here is the fragment of my adoption file for year 2000:

adoptionDatecusip
2000060193166410
2000060260456710
200006052343610
20000605432510

I am also attaching the fragment of the Institutional Ownership data... Fdate is the date you are referring to as OwnDate.

I also need to date to be the LAST day of the quarter before the adoption.

Thank you for your help.

AnaV

Attachment
Respected Advisor
Posts: 4,659

Re: Calculate the mean in the quarter before the event date

What would be the expected output for those 4 cases? - PG

PG
Contributor
Posts: 20

Re: Calculate the mean in the quarter before the event date

If you consider the first two companies:

adoptionDatecusip
2000060193166410
2000060260456710

the for the first one, I would expect the total institutional ownership to be 0.2532 ( 25%) and for the second one 0.65515 ( 65%) based on the fact that both adoptions occurred in the second quarter of 2000 and the sum of institutional ownership was calculated at the end of the first quarter of 2000. Instown is a percentage, so the value has to be lower than 1.

Respected Advisor
Posts: 4,659

Re: Calculate the mean in the quarter before the event date

I get the correct answer with the following query :

proc sql;

create table qtrOwn as

select

     a.cusip,

     a.adoptionDate,

     intnx("QTR",a.adoptionDate,-1,"END") as preQtrDate format=yymmdd10.,

     sum(i.instOwn) as  qtrInstOwn format=percent7.1

from

     adoption as a left join

     instOwn2000 as i

on

     a.cusip = i.cusip and

     intnx("QTR",a.adoptionDate,-1) = intnx("QTR",i.fDate,0)

group by

     a.cusip,

     a.adoptionDate,

     preQtrDate;

select * from qtrOwn;

quit;

PG

PG
Contributor
Posts: 20

Re: Calculate the mean in the quarter before the event date

I tried applying these codes to my dataset and I get the following message:

NOTE: Invalid argument to function INTNX. Missing values may be generated.

NOTE: Invalid argument to function INTNX. Missing values may be generated.

NOTE: Invalid argument to function INTNX. Missing values may be generated.

This is the outup I get... As you see some of the percentage values are very high, far above 100%, which should not be the case.

adoptiondate

00036010

cusip

19990219

preQtrDate

.

.qtrInstOwn

0004001019980224..
0007521019950929..
0007941019971003..
00087X1019990621.52.7%
0009551019991119..
0010841019940126..
0012041019960306..
0015471019960123..
0015751019950112..
0017351019981103..
0019031019900814..
0019341019950216..
00204C1019970714.448%
00207M1019980312.69.3%
00253A1019960624.1260%
Respected Advisor
Posts: 4,659

Re: Calculate the mean in the quarter before the event date

I suspect the problem is with the way you read in the dates. You should check the data type and format associated with date fields in datasets like INSTOWN2000Q1. They should be numeric with a date format.

Here is how I read your txt file :

data adoption;
attrib adoptionDate format=yymmdd10. informat=yymmdd8.;
input adoptionDate cusip;
datalines;
20000601 93166410
20000602 60456710
20000605 2343610
20000605 432510
;

data instown2000;
infile "&sasforum.\datasets\instown2000.txt" dsd firstobs=2;
attrib fdate rdate format=yymmdd10. informat=yymmdd8.;
length mgrName stkName $64 stkcdesc ticker $5;
input fdate mgrname typecode rdate cusip shares change stkname ticker stkcdesc prc shrout1
     shrout2 shrout3 instown;
run;

the informat yymmdd8. associated with date variables tells SAS how to interpret something like 20000331 as it is read in. Without it, the date fields are just read as numbers, not dates.

PG

PG
Contributor
Posts: 20

Re: Calculate the mean in the quarter before the event date

Hi PGStats,

I doubled check my formats and my fdate was in the Best12. format so I transformed it into yymmdd8. format the same as my adoptionDate.

1adoptionDateNum8YYMMDD10.YYMMDD8.
8changeNum8BEST12.BEST32.
2cusipChar8$8.$8.
3fdateNum8YYMMDD10.YYMMDD8.
15instownNum8BEST12.F12.
4mgrnameChar24$24.$24.
11prcNum8BEST12.BEST32.
6rdateNum8BEST12.BEST32.
7sharesNum8BEST12.BEST32.
12shrout1Num8BEST12.BEST32.
14shrout3Num8BEST12.F12.
13stkcdescChar3$3.$3.
9stknameChar28$28.$28.
10tickerChar4$4.$4.
5typecodeNum8BEST12.BEST32.

I also created a merged dataset where I merged the adoption dataset with the institutional ownership dataset to have it all in one place. When I run the proc sql statement I get a table which shows me the right Cusips, AdoptionDates and PreQtrDates, but it does not sum up the instown and shows nothing in the column of qtrOwn. It gives me the following notes:

proc sql;

create table qtrOwn as

select

     cusip,

     adoptionDate,

     intnx("QTR",adoptionDate,-1,"END") as preQtrDate format=yymmdd10.,

     sum(instOwn) as  qtrInstOwn format=percent7.1,

  intnx("QTR",adoptionDate,-1) = intnx("QTR",fDate,0)

from

     merged90.merged90s;

group by

     cusip,

     adoptionDate,

     preQtrDate;

select * from qtrOwn;

quit;

NOTE: The query requires remerging summary statistics back with the original data.

NOTE: Invalid argument to function INTNX. Missing values may be generated.

NOTE: Table WORK.QTROWN created, with 2021026 rows and 5 columns.

Other times proc sql takes a very long time to complete and freezes after some time. I don't know if it's because it is calculating the sum instown for every cusip or something is not working. Is there a way to make it run faster?

Thank you for your help,

AV

Respected Advisor
Posts: 4,659

Re: Calculate the mean in the quarter before the event date

Keeping adoption and ownership data in separate datasets was a better approach. You must associate the informat with the date variables at the reading stage to get proper SAS dates.  Try proc print data=adoption(obs=10); run; to confirm the dates validity. Do the same with the ownership dataset. - PG

PG
Contributor
Posts: 20

Re: Calculate the mean in the quarter before the event date

The only problem is that my inst.ownership dataset is already a combined dataset, which includes at least 20merged datasets, such as q1_200, q2_200, etc. You are right that my fdate appears as *****. I try to change the date format, but it does not seem to work. When doing proc contents the table shows me the that for fdate my format is the yymmdd10. and informat is yymmdd8.

Is there a way to change the date format in the merged dataset, inst. ownership instead of typing in the input for every single imported file?

Respected Advisor
Posts: 4,659

Re: Calculate the mean in the quarter before the event date

Try to expand the width of the fDate column to see its content. - PG

PG
Contributor
Posts: 20

Re: Calculate the mean in the quarter before the event date


I fixed the dates and tried to run it for year 2000. I dint get anymore error messages concerning Intnx function , in fact my procedure is performd without any errors, but I don't get an outup for qtrown.

cusip        adoptiondate   preqtrdate qtrinstown

000360101999-02-191998-12-31   .
000400101998-02-241997-12-31.
000752101995-09-291995-06-30.
000794101997-10-031997-09-30.
00087X101999-06-211999-03-31.
000955101999-11-191999-09-30.
001084101994-01-261993-12-31.
001204101996-03-061995-12-31.
001547101996-01-231995-12-31.
001575101995-01-121994-12-31.
001735101998-11-031998-09-30.
001903101990-08-141990-06-30

My code:

data INSTOWNq1_2000;
infile "E:\SAS Institutional Ownership\46_q1_2000.csv"  dsd firstobs=2;
attrib fdate rdate format=yymmdd10. informat=yymmdd8.;
length mgrName stkName $64 stkcdesc ticker $5;
input fdate mgrname typecode rdate cusip $ shares change stkname ticker stkcdesc prc shrout1
     shrout2;
run;

data INSTOWNq2_2000;
infile "E:\SAS Institutional Ownership\45_q2_2000.csv"  dsd firstobs=2;
attrib fdate rdate format=yymmdd10. informat=yymmdd8.;
length mgrName stkName $64 stkcdesc ticker $5;
input fdate mgrname typecode rdate cusip $ shares change stkname ticker stkcdesc prc shrout1
     shrout2;
run;


data INSTOWNq3_2000;
infile "E:\SAS Institutional Ownership\44_q3_2000.csv"  dsd firstobs=2;
attrib fdate rdate format=yymmdd10. informat=yymmdd8.;
length mgrName stkName $64 stkcdesc ticker $5;
input fdate mgrname typecode rdate cusip $ shares change stkname ticker stkcdesc prc shrout1
     shrout2;
run;

data INSTOWNq4_2000;
infile "E:\SAS Institutional Ownership\43_q4_2000.csv"  dsd firstobs=2;
attrib fdate rdate format=yymmdd10. informat=yymmdd8.;
length mgrName stkName $64 stkcdesc ticker $5;
input fdate mgrname typecode rdate cusip $ shares change stkname ticker stkcdesc prc shrout1
     shrout2;
run;

data instown2000;
set WORK.INSTOWNq1_2000 WORK.INSTOWNq2_2000 WORK.INSTOWNq3_2000 WORK.INSTOWNq4_2000 ;
shrout3=shrout1*1000000;
instown=shares/shrout3;
if shrout3=0 or shrout3=. then delete;
drop prdate;
if stkcdesc= 'COM'or stkcdesc='CMA' or stkcdesc='CMB' or stkcdesc='CMC' or stkcdesc=''  ;
run;

proc sort data=instown2000;
by cusip;
run; quit;

data adoption2000;
infile "E:\SAS Adoptions/AdoptionCusip2000_2010.csv" dsd firstobs=2;
attrib adoptiondate format=yymmdd10. informat=yymmdd8.;
input adoptiondate cusip $;
run;
quit;

proc sort data=adoption2000;
by cusip;
run; quit;

proc sql;

create table qtrOwn as
select

     a.cusip,

     a.adoptionDate,

     intnx("QTR",a.adoptionDate,-1,"END") as preQtrDate format=yymmdd10.,

     sum(i.instOwn) as  qtrInstOwn format=percent7.1
from

     adoption2000 as a left join
  instOwn2000 as i

on

     a.cusip = i.cusip and
intnx("QTR",a.adoptionDate,-1) = intnx("QTR",i.fDate,0)

group by

     a.cusip,

     a.adoptionDate,

     preQtrDate;

select * from qtrOwn;

quit;

LOG

NOTE: The SAS System was unable to open the macro library referenced by the SASMSTORE = libref
      SASUSER.
271  select
272
273       a.cusip,
274
275       a.adoptionDate,
276
277       intnx("QTR",a.adoptionDate,-1,"END") as preQtrDate format=yymmdd10.,
278
279       sum(i.instOwn) as  qtrInstOwn format=percent7.1
280  from
281
282       adoption2000 as a left join
283       instOwn2000 as i
284
285  on
286
287       a.cusip = i.cusip and
288      intnx("QTR",a.adoptionDate,-1) = intnx("QTR",i.fDate,0)
289
290  group by
291
292       a.cusip,
293
294       a.adoptionDate,
295
296       preQtrDate;
NOTE: Table WORK.QTROWN created, with 2010 rows and 4 columns.

297
298  select * from qtrOwn;
NOTE: The SAS System was unable to open the macro library referenced by the SASMSTORE = libref
      SASUSER.
299
300  quit;
NOTE: The SAS System was unable to open the macro library referenced by the SASMSTORE = libref
      SASUSER.
NOTE: PROCEDURE SQL used (Total process time):
      real time           47.15 seconds
      cpu time            1.98 seconds


NOTE: This SAS session is using a registry in WORK.  All changes will be lost at the end of
      this session.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 24 replies
  • 1122 views
  • 6 likes
  • 3 in conversation