Date:         Sat, 30 Dec 2006 12:18:58 -0500
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Lag or array question

On Fri, 29 Dec 2006 10:01:44 -0500, Peter Flom <Flom@NDRI.ORG> wrote:

>Hello
>
>I have a data set that has (among other things) the following structure
>
>MSA    Year             Unemp
>1         1990              5.1
>1         1991              6.0
>
>... (through 2002)
>2         1990               4.1
>2         1991                4.2
>
>..... (for 96 MSAs)
>
>I would like to create a variable of lagged unemployment by 2 years.  So,
lagunep for 1992 would be unemp for 1990, for each MSA.
>
>I'm sure this is easy for the data step gurus, but I can't see how best to
do it.
>
>(for context, I will then be using lagunemp in  a multilevel model using
other variables in the data set.
>
>
>TIA and HNY
>
>Peter
>
>
>
>Peter L. Flom, PhD
>Assistant Director, Statistics and Data Analysis Core
>Center for Drug Use and HIV Research
>National Development and Research Institutes
>71 W. 23rd St
>http://cduhr.ndri.org
>www.peterflom.com
>New York, NY 10010
>(212) 845-4485 (voice)
>(917) 438-0894 (fax)

With Version 9, you can make it tidier and avoid creating any intermediate
variables.

Tested code:

   data want;
   set have;
   lagunep = ifn(msa=lag2(msa),lag2(unemp),. );
   run;

If LAGUNEP is only needed once in the code and not needed in the output, you
can even avoid creating it and just plug its formula into the other code, as in

   data want2;
   set have;
   maxu = max( ifn(msa=lag2(msa),lag2(unemp),. ),unemp) ;
   run;

It can be done with SQL also. Then, presuming that subsequent processing is
to be done in a DATA step, a view is convenient:

   proc sql;
   create view withlag as
    select  *
          , (select unemp
              from have
              where msa=outer.msa and year=outer.year-2
            ) as lagunep
     from have as outer;
   quit;

Just process the view and the new variable is available, as in

   data want3;
   set withlag;
   maxu = max(lagunep,unemp);
   run;