Help using Base SAS procedures

lead & lag-proc expand

Accepted Solution Solved
Reply
Contributor mei
Contributor
Posts: 62
Accepted Solution

lead & lag-proc expand

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;

Attachment

Accepted Solutions
Solution
‎06-04-2012 01:59 AM
Super User
Posts: 10,028

Re: lead & lag-proc expand

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

View solution in original post


All Replies
Solution
‎06-04-2012 01:59 AM
Super User
Posts: 10,028

Re: lead & lag-proc expand

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

Respected Advisor
Posts: 3,799

Re: lead & lag-proc expand

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;

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 2117 views
  • 0 likes
  • 3 in conversation