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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

4 REPLIES 4
PGStats
Opal | Level 21

What do you want as an output?

PG
JLee
Calcite | Level 5

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
Onyx | Level 15

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
Calcite | Level 5

Thank you!!!!!!!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 2072 views
  • 0 likes
  • 3 in conversation