creating YYYYMM variables based on certain YYYYMM.

[JKCho]

do i=1 to 18 by 1;
mm1=mm-i;
if mm1>0 then M_i=yyyy*100+mm1;
if -12<mm1<=0 then M_i=(yyyy-1)*100+mm1+12;
if mm1<=12 then M_i=(yyyy-2)*100+mm1+18;
end;
run;

Hello,

I know the code above totally wrong and still working on it. Meanwhile, I need your help as I feel I cannot do that myself.

What I want to do is to create multiple M_i variables which are several months ahead based on specific other dates.

Examples are: M_18 mean 18 months ahead, and M_1 is 1 month ahead.

YYYMM   M_18     M_1

199808   199702   199807

199801   199607   199712

What I am struggling with are...

1. I do not know how to create variables that also vary by loop statement with "i".

I want to create M_18 to M_1, and think there is a way to use simple programming instead of designating M_1 to M_18 each. 

2. I think that the do loop above does not work because I just write M_i, how do I make loop or iteration happen there?

If not using do loop, I must write M_1 = blahblah to M_18 = blahblah. This was what I first did and changed my mind to work in this way but hardly made it.

I appreciate your help a lot!!

[PaigeMiller]

Learn to use ARRAYs, this will allow you to do computations over many variables (in your case, 18 times);

Learn to use SAS Dates instead of text strings or integers like 199808. SAS dates make it easy to increment by one month (that's the INTNX function) and they know that if you increment December by one month, it is January of the next year.

Learn to use SAS formats so that the values appear as you want, specifically 199808, for example.

data have;
    startdate='01AUG1998'd;
run;

data want;
    set have;
    array m m_1-m_18;
    do i=1 to 18;
        m(i) = intnx('month',startdate,i-1,'b');
    end;
    format m_1-m_18 yymmn6.;
run;

 Lastly, such an arrangement of data, with 18 month variables M_1 through M_18, is usually a very poor arrangement for further analysis (except for regression or similar modeling). You would be much better with the long arrangement of data, this will make your next steps much much easier.

data long;
    set have;
    do i=1 to 18;
        month = intnx('month',startdate,i-1,'b');
        output;
    end;
    format month yymmn6.;
    drop i startdate;
run; 

[Tom · Accepted solution]

I really cannot tell what you are doing at all.

If you want to work with DATE values then you should use DATE variables.  You can then always convert the DATE values into the YYYYMM style variables.

So if you are using integers in the form YYY,YMM to represent dates. 

For example like this:

data have;
  input YYYMM expect_18 expect_1;
cards;
199808   199702   199807
199801   199607   199712
;

So you could use something like this to convert those YYYMM values into dates, calculate new date, and convert the dates back into those goofy values:

data want ;
  set have;
  date = input(put(yyymm,Z6.)||'01',yymmdd8.);
  date_m1 = intnx('month',date,-1);
  date_m18 = intnx('month',date,-18);
  m_1 = year(date_m1)*100+month(date_m1);
  m_18 = year(date_m18)*100+month(date_m18);
  format date: date9.;
run;  

Results

                 expect_
Obs     YYYMM       18      expect_1         date      date_m1     date_m18      m_1      m_18

 1     199808     199702     199807     01AUG1998    01JUL1998    01FEB1997    199807    199702
 2     199801     199607     199712     01JAN1998    01DEC1997    01JUL1996    199712    199607