topic Re: lead & lag-proc expand in SAS Procedures

lead & lag-proc expand

mei — Mon, 04 Jun 2012 04:42:33 GMT

Dear Sir,

I am using proc expand to calculate leads and lags of advertising expenses.

I found out that for one particular data with the following gvkey (company codes)

gvkey Fyear Adv Adv_lead1

01164 2001 0 152

01164 2004 152 0

Pls note there is no data for this company for year 2002 and 2003.

Therefore my question is for year 2001, its value for lead 1 should be for 2002 which is missing value, by with the proc expand program, the result turn out to be picking the value from 2004, which appear subsequent to 2001 data. How do we avoid this to happen?

This is the program i ran:

proc expand data=spi.spi8

out=advtest

method = none;

by gvkey;

id fyear;

convert xad = adv_lag1 / transformout=(lag 1);

convert xad = adv_lag2 / transformout=(lag 2);

convert xad = adv_lag3 / transformout=(lag 3);

convert xad = adv_lag4 / transformout=(lag 4);

convert xad = adv_lead1 / transformout=(lead 1);

convert xad;

run;

Re: lead & lag-proc expand

Ksharp — Mon, 04 Jun 2012 05:59:56 GMT

An approach is making some obs to pad this gap .

libname x v9 'c:\';
proc sql;
create table a as
 select * 
  from (select distinct FYEAR from x.Advtest), (select distinct GVKEY from x.Advtest) ;

quit;
data want;
 merge a x.Advtest;
 by  gvkey  FYEAR;
run;

Ksharp

Re: lead & lag-proc expand

data_null__ — Mon, 04 Jun 2012 19:42:02 GMT

PROC EXPAND can create these missing years. You will need to make FYEAR a SAS date variable, but it should be anyway. I can't figure how to do the lags in the same step as the interpolation.

data spi;

input gvkey Fyear xad;

fyear = mdy(1,1,fyear);

format fyear year.;

cards;

01164 2001 0 152

01164 2004 152 0

;;;;

run;

proc expand data=spi out=advtest to=year method=none;

by gvkey;

id fyear;

run;

proc expand data=advtest out=advlag method=none;

by gvkey;

id fyear;

convert xad = adv_lag1 / transformout=(lag 1);

convert xad = adv_lag2 / transformout=(lag 2);

convert xad = adv_lag3 / transformout=(lag 3);

convert xad = adv_lag4 / transformout=(lag 4);

convert xad = adv_lead1 / transformout=(lead 1);

convert xad;

run;

proc print;

run;