BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mei
Calcite | Level 5 mei
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

2 REPLIES 2
Ksharp
Super User

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

data_null__
Jade | Level 19

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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