lag/lead question

[JLee]

Hello,

I am trying to create a lag/lead of one month for a large data set which spans from 2000-2010. I seem to be doing something wrong and was wondering if someone could please help me. I have attached a subset of my data. I tried to use a proc expand step and the output table was blank.

Thank you for your time!

Jessie

[PGStats]

What do you want as an output?

[JLee]

I realized that I also forgot some columns. I would like the output to show a lag and lead of Mean_Noth, Mean_D0, Mean_D1, Mean_D2, Mean_D3 and Mean_D4 as it relates to Total_Pos, Total_Neg, Pos_W, and Neg_W. I am trying to shdetermine if the Mean_ categories will effect the Total_Pos, Total_Neg, Pos_W and Neg_W.

Thank you!

[Haikuo · Accepted solution]

There can be many ways, but since you mentioned proc expand, here is one solution based on it:

I used cards input for the purpose of illustration, you need to import it.

data have;

input (DATE FIPS County     State) (:$)     Mean_Noth     MEAN_D0     MEAN_D1     MEAN_D2     MEAN_D3     MEAN_D4;

cards;

20001     01001     AUTAUGA     AL     0     100     75     0     0     0

20002     01001     AUTAUGA     AL     0     100     0     0     0     0

20003     01001     AUTAUGA     AL     0     100     31.87     0     0     0

20004     01001     AUTAUGA     AL     1.7775     98.2225     0     0     0     0

20005     01001     AUTAUGA     AL     0     100     80.956     2.334     0     0

20006     01001     AUTAUGA     AL     0     100     100     75.9725     4.72     0

20007     01001     AUTAUGA     AL     0     100     100     100     74.4825     42.825

20008     01001     AUTAUGA     AL     0     100     100     100     100     100

20009     01001     AUTAUGA     AL     0     100     100     100     100     93.025

200010     01001     AUTAUGA     AL     0     100     100     100     100     88.84

200011     01001     AUTAUGA     AL     0     100     100     100     75     0

200012     01001     AUTAUGA     AL     0     100     100     100     0     0

200011     37005     ALLEGHANY     AL     0     0     0     0     0     0

200012     37005     ALLEGHANY     AL     0     0     0     0     0     0

20011     37005     ALLEGHANY     AL     0     0     0     0     0     0

20012     37005     ALLEGHANY     AL     0     0     0     0     0     0

20013     37005     ALLEGHANY     AL     0     0     0     0     0     0

20015     37005     ALLEGHANY     AL     0     0     0     0     0     0

20016     37005     ALLEGHANY     AL     0     0     0     0     0     0

20017     37005     ALLEGHANY     AL     0     0     0     0     0     0

20018     37005     ALLEGHANY     AL     0     0     0     0     0     0

20019     37005     ALLEGHANY     AL     0     0     0     0     0     0

200110     37005     ALLEGHANY     AL     0     0     0     0     0     0

200111     37005     ALLEGHANY     AL     0     0     0     0     0     0

200112     37005     ALLEGHANY     AL     0     0     0     0     0     0

;

proc expand data=have out=want;

Convert Mean_Noth=Lead_Mean_Noth /transformout=(lead 1);

Convert MEAN_D0=Lead_MEAN_D0 /transformout=(lead 1);

Convert MEAN_D1=Lead_MEAN_D1 /transformout=(lead 1);

Convert MEAN_D2=Lead_MEAN_D2 /transformout=(lead 1);

Convert MEAN_D3=Lead_MEAN_D3 /transformout=(lead 1);

Convert MEAN_D4=Lead_MEAN_D4 /transformout=(lead 1);

Convert Mean_Noth=Lag_Mean_Noth /transformout=(Lag 1);

Convert MEAN_D0=Lag_MEAN_D0 /transformout=(Lag 1);

Convert MEAN_D1=Lag_MEAN_D1 /transformout=(Lag 1);

Convert MEAN_D2=Lag_MEAN_D2 /transformout=(Lag 1);

Convert MEAN_D3=Lag_MEAN_D3 /transformout=(Lag 1);

Convert MEAN_D4=Lag_MEAN_D4 /transformout=(Lag 1);

run;

proc print;run;

Regards,

Haikuo

[JLee]

Thank you!!!!!!!