Date:         Tue, 27 May 2008 09:05:18 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Subject:      Re: Calculating previous month value

On Tue, 27 May 2008 06:07:42 -0400, Harry Sas <dj_epw@YAHOO.COM> wrote:

>On Tue, 27 May 2008 02:32:45 -0700, Chris Jones <chrisj75@GMAIL.COM> wrote:
>
>>On 27 May, 09:31, dj_...@YAHOO.COM (Harry Sas) wrote:
>>> Hi
>>>
>>> I have the following master dataset.
>>>
>>> Master Dataset
>>> -----------------
>>> Date      Current_Month_Value
>>> Feb2008     2
>>> Jul2005     6
>>> Oct2001     3
>>> Jan2007     5
>>> Jan2008     1
>>> Jun2005     7
>>> Sep2001     4
>>> Dec2006     8
>>>
>>> Using the master dataset, I want to create the output dataset with two
>new
>>> variables Previous_Month & Previous_Month_Value.
>>>
>>> For e.g, if Date is Feb 2008 then Previous_Month should be Jan 2008 with
>>> its corresponding value displayed in Previous_Month_Value.
>>>
>>> Output Dataset
>>> ----------------
>>> Date   Current_Month_Value Previous_Month Previous_Month_Value
>>> Feb2008        2                Jan2008             1
>>> Jul2005        6                Jun2005             7
>>> Oct2001        3                Sep2001             4
>>> Jan2007        5                Dec2006             8
>>>
>>> Can anyone suggest me an approach?
>>>
>>> Thanks in anticipation.
>>>
>>> Harry
>>
>>Here's the obvious solution....
>>
>>data
>>months ;
>>  input @1 date
>>monyy7. ;
>>
>>  current_month_value =
>>month(date) ;
>>
>>  previous_month =
>>intnx('month',date,-1,'beginning') ;
>>  previous_month_value =
>>month(previous_month) ;
>>
>>  format date previous_month
>>monyy7. ;
>>
>>datalines ;
>>feb2008
>>jul2005
>>oct2001
>>jan2007
>>;
>>run ;
>
>---------------------------------------------------------------------------
>
>That's not what I was looking for.
>
>Variable names have caused some confusion - if you look at the dataset,
>Current_month_value is not the numeric equivalent of Current_month but it
>is just a number.
>
>What I was thinking of was a self-join by creating previous_month using
>intnx function. I am wondering if there is an alternate approach.

The self-join works. Data:

   data have;
   input Date : monyy7. Current_Month_Value;
   format date monyy7.;
   cards;
   Feb2008     2
   Jul2005     6
   Oct2001     3
   Jan2007     5
   Jan2008     1
   Jun2005     7
   Sep2001     4
   Dec2006     8
   ;

Code:

   proc sql;
   create table want as
    select current.*, lookback.Current_Month_Value
                      as Previous_Month_Value
    from ( select *, intnx('month',have.date,-1)
                     as Previous_Month format=monyy7.
           from have
         ) as current
         join
         have as lookback
          on Previous_Month = lookback.date;
   quit;

Result:

              Current_                 Previous_
               Month_     Previous_      Month_
      Date      Value       Month        Value

   FEB2008        2        JAN2008         1
   JUL2005        6        JUN2005         7
   OCT2001        3        SEP2001         4
   JAN2007        5        DEC2006         8

To my surprise, it even preserves the original order.