topic Formating date before merge in SAS Procedures

Formating date before merge

LanMin — Tue, 26 May 2015 16:36:50 GMT

Dear all,

My task is to merge two tables based on a common variable (permno) conditioned on the effective dates for this variable. The date variable must be manipulated before the merge can be done.

Attached datasets provide examples of my data.

My goal is to add gvkey to my PermnoData. To do this, I will merge by permno two times. First time, I will rename supplierpermno to permno, (supplierpermno is the permno of a firm that acts as a supplier, think of a company that supplies the glass to Apple for IPHONE product). I need your help with date manipulation.

The date variables are in different formats in two datasets:

PermnoData fiscalyearenddate length8, format best 12. informat best32. ,

FirmData firsteffectivedate lasteffectivedate length8, YYMMDDN8.

11346 79363 record has the date 19950331: this means that this supplier customer pair relation is effective for fiscal year ending march 31 1995, (fiscal year starts 364 days earlier than 3/31/195, which is 4/1/1994, ). The concept of fiscal year : some firms do not have their fiscal year start on Jan. 1 and end on Dec. 31. For these firms , we compute its fiscal year the following way, if the fiscal year end month is May or earlier, then fiscal year is calendar minus 1. So my 11346 -79363 pair has a fiscal year of 1994 and 1995 for the two records.

My goal is to add gvkey 28272 (from firmdata to PermnoData) as long as the fiscalyear matches within the range of firsteffectivedate and lasteffectivedate. In firmdata, 79363 is effective between 19930607 and 20120331.

I need your help with coding:

Do I need generate a fiscalyearbegdate for each row in Permnodata? How do I do that?
After merge is done , how do I create a fiscal year measure based on fiscalyearenddate?

Thank you,

Lan

Re: Formating date before merge

ballardw — Tue, 26 May 2015 18:24:49 GMT

First thing will be getting firsteffectivedate and lasteffectivedate as date values so they can be manipulated easier. Best would have been reading them from the source as dates but

firsteffectivedate = input(put(firsteffectivedate, best8.),yymmdd8.); and similar for the other variable.

Somewhere you're going to have to supply WHICH suppliers use which fiscal year.

Comparisons can involve shifts using INTNX and INTCK functions.

Re: Formating date before merge

Tom — Tue, 26 May 2015 22:28:06 GMT

Why are the formats attached to the date variables different? Can you fix that first?

If you read the string '19950331' as a number then SAS will store it as the number 19,950,331. But if you read it using the informat YYMMDD then SAS will store it as the number 12,873. So you should convert 19,950,331 to a SAS date value using the INPUT function.

INPUT(cats(fiscalyearenddate),yymmdd8.)

Once you get the dates using the same system then you need to clarify how you want to combine the data. Do you want the value of GVKEY for the records where PERMNO matches the CustomerPermo or the SupplierPermno? Or both?

Perhaps something like this?

data Permno ;

length supplierpermno customerpermno fiscalyearenddate 8;

informat fiscalyearenddate YYMMDD10. ;

format fiscalyearenddate YYMMDD10. ;

input supplierpermno customerpermno fiscalyearenddate;

cards;

11346 79363 19950331

11346 79363 19960331

;;;;

data firm;

length gvkey $6 permno firsteffectivedate lasteffectivedate 8 ;

informat firsteffectivedate lasteffectivedate yymmdd10.;

format firsteffectivedate lasteffectivedate yymmdd10.;

input gvkey permno firsteffectivedate lasteffectivedate ;

cards;

13393 11346 19870519 19990331

28272 79363 19930607 20120331

;;;;

proc sql noprint ;

create table want as

select a.*,b.gvkey as SupplierGVKey,c.gvkey as CustomerGVKey

from permno a

left join firm b

on a.supplierpermno = b.permno

and a.fiscalyearenddate between b.firsteffectivedate and b.lasteffectivedate

left join firm c

on a.customerpermno = c.permno

and a.fiscalyearenddate between c.firsteffectivedate and c.lasteffectivedate

;

quit;

Supplier

permno

Customer

permno

Fiscalyear

enddate

Supplier

GVKey

Customer

GVKey

11346

79363

1995-03-31

13393

28272

11346

79363

1996-03-31

13393

28272

Re: Formating date before merge

LanMin — Wed, 27 May 2015 00:46:26 GMT

Hi Tom,

Thank you for your nice coding ! the Proc sql part is especially helpful .

Let me answer your first question.

The attached file from my #1 post are examples of my sample data. My data was downloaded from commercial database, and the 2 date variables from firmdata is different from the date variable from permno data. I am not proficient with sas date,so I did not know how to convert them into the same format.

I see you used my txt file to input the data using the same format and informat. My data are already in sas data format, and I agree with ballardw #2 post , using firsteffectivedate = input(put(firsteffectivedate, best8.),yymmdd8.) which is the same as your suggested method of using INPUT(cats(fiscalyearenddate),yymmdd8.).

thanks!

Re: Formating date before merge

LanMin — Wed, 27 May 2015 02:10:46 GMT

Hi all,

Following Tom's post above, I have merged the two data using proc sql, it contains endatefiscalyear YYMMDD10.

how do I generate the fiscal year from this variable.

fiscal year is defined as follows:

suppose endatefiscalyear = 19990531

if a company's fiscal year end month is on or before may, then fiscal year is calendar year minus 1, in above example, this company fiscal year is 1998,

if a company's fiscal year end month is on or after June, then fiscal year is the same as calendar year , in following example,

endatefiscalyear = 19990831

this company fiscal year is 1999.

could you show me the function to generate fiscal year based on above rule and endatefiscalyear YYMMDD10. ?

thanks,

Lan