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;
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
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
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.