BookmarkSubscribeRSS Feed
SAShole
Pyrite | Level 9

Hey SAS'ers,

     I'm trying to use a lag function within by groups. please see my desired output for an idea of what i'm going for.

Thank you!

/*Begin Code*/

%let intercept = 0.236917;

%let unemployment = 0.010734;

%let permits = -0.03398;

Data Have;

  input  State $ i urate bp date Initial_Vrate;

  informat State $10. i urate bp  date  Initial_Vrate  Best12.;

  format date monyy. urate Initial_Vrate percent8.2;

cards;

Alabama 1 0.11766 6.78106 18263 0.13061

Alabama 2 0.11367 6.77765 18294 0.13061

Alabama 3 0.10847 7.20266 18322 0.13061

Alabama 4 0.09303 7.04054 18353 0.13061

Alabama 5 0.09025 6.96319 18383 0.13061

Alaska 1 0.09307 3.17805 18263 0.14659

Alaska 2 0.09553 3.80666 18294 0.14659

Alaska 3 0.09407 3.82864 18322 0.14659

Alaska 4 0.08254 4.60517 18353 0.14659

Alaska 5 0.07608 5.05625 18383 0.14659

Arizona 1 0.09728 7.13728 18263 0.1616

Arizona 2 0.09774 7.02198 18294 0.1616

Arizona 3 0.09384 7.42536 18322 0.1616

Arizona 4 0.09727 7.04054 18353 0.1616

Arizona 5 0.09691 6.88244 18383 0.1616

;

run;

data want;

  set have;

  by state;

  if first.state then Modeled_Vacancy = Initial_Vrate;

  else Modeled_Vacancy = lag(Modeled_Vacancy) + &intercept. + (&unemployment. *lag(urate)) + (&permits. *lag(bp));

format Modeled_Vacancy percent8.2;

run;

/* End of Code - Output should look like the following: */

Stateiuratebpdateinitial_vrateModeled_Vrate
Alabama111.8%6.78106Jan-1013.1%13.1%
Alabama211.4%6.77765Feb-1013.1%13.8%
Alabama310.8%7.20266Mar-1013.1%14.6%
Alabama49.3%7.04054Apr-1013.1%14.0%
Alabama59.0%6.96319May-1013.1%13.8%
Alaska19.3%3.17805Jan-1014.7%14.7%
Alaska29.6%3.80666Feb-1014.7%27.7%
Alaska39.4%3.82864Mar-1014.7%38.5%
Alaska48.3%4.60517Apr-1014.7%49.3%
Alaska57.6%5.05625May-1014.7%57.4%
Arizona19.7%7.13728Jan-1016.2%16.2%
Arizona29.8%7.02198Feb-1016.2%15.7%
Arizona39.4%7.42536Mar-1016.2%15.6%
Arizona49.7%7.04054Apr-1016.2%14.2%
Arizona59.7%6.88244May-1016.2%14.1%
4 REPLIES 4
Tom
Super User Tom
Super User

Do NOT call LAG() function conditionally.  Search for the thousands of threads that explain how LAG() works.

For your example you might be able to solve this by putting the IF FIRST..... statement AFTER the one that uses the lagged values.

Modeled_Vacancy = lag(Modeled_Vacancy) + &intercept. + (&unemployment. *lag(urate)) + (&permits. *lag(bp));

if first.state then Modeled_Vacancy = Initial_Vrate;


Not sure about how LAG() works for a calculated variable.  You might want to just RETAIN the value instead.

retain modeled_vacancy ;

Modeled_Vacancy = Modeled_Vacancy + &intercept. + (&unemployment. *lag(urate)) + (&permits. *lag(bp));

if first.state then Modeled_Vacancy = Initial_Vrate;

Haikuo
Onyx | Level 15

Following Tom's idea, while using ifn(), which unconditionally lagging:

data want;

  set have;

  by state;

  retain  Modeled_Vacancy;

Modeled_Vacancy=ifn(first.state, Initial_Vrate,  Modeled_Vacancy + &intercept. + (&unemployment. *lag(urate)) + (&permits. *lag(bp)));

format Modeled_Vacancy percent8.1;

run;

Haikuo

Linlin
Lapis Lazuli | Level 10

Hi,

Mike explained using Lag Function with condition in the link below:

https://communities.sas.com/thread/35037

Peter_C
Rhodochrosite | Level 12

to answer the original posting, reverse the order of these two lines

if first.state then Modeled_Vacancy = Initial_Vrate;

else Modeled_Vacancy = lag(Modeled_Vacancy) + &intercept. + (&unemployment. *lag(urate)) + (&permits. *lag(bp));

replace with

Modeled_Vacancy = lag(Modeled_Vacancy) + &intercept. + (&unemployment. *lag(urate)) + (&permits. *lag(bp));

if first.state then Modeled_Vacancy = Initial_Vrate;

that seems the simplest change that overcomes the LAG() problem

(notice there is now no ELSE so the LAG()s become "unconditional")

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 4822 views
  • 7 likes
  • 5 in conversation