joining with previous month

[Myurathan]

I have following table

I want to have previous month data joined to the table. something like this.

Please help me.

[ballardw · Accepted solution]

I assume that you want this within values of ID (you did not state that though the example implies that).

Are there by any chance other dates in Jan2018 for those ids?

If not then this might get you started:

proc sql;
   create table want as
   select a.date, a.id, a.value_01, a.value_02
         ,b.date as Prv_date, b.value_01 as Prv_value_01
         , b.value_02 as Prv_value_02
   from have as a
        left join
        have as b
        on a.id=b.id
        and intnx('month',a.date,-1,'B') = intx('month',b.date,0,'B')
   ;
quit;

if you have dates for the same Id such as in Dec 2017 the Jan2018 will be linked to them as well.

[Reeza]

Where intnx('month', t1.date, 1, 'e') = t2.date

Use INTNX to increment the date to the last of the next month and see if it matches the date in table 2.

---

@Myurathan wrote:

I have following table

I want to have previous month data joined to the table. something like this.

Please help me.

---

[novinosrin]

@Myurathan If you could paste your data as plain text, I could copy paste on to my sas and work on a solution. I am afraid i am too lazy to type from pics/screen shots. Just a request going forward in future. Thank you!

[Myurathan]

data date_test;
input Date: date9. ID Value_01 Value_02;
format date date9.;
datalines;
31Jan2018 001 1000 2000
31Jan2018 002 1500 2500
28Feb2018 001 5000 6000
28Feb2018 002 3000 5800
;

[novinosrin]

data date_test;
input Date: date9. ID Value_01 Value_02;
format date date9.;
datalines;
31Jan2018 001 1000 2000
31Jan2018 002 1500 2500
28Feb2018 001 5000 6000
28Feb2018 002 3000 5800
;

proc sort data=date_test out=_date_test;
by id date;
run;

data want;
if _n_=1 then do;
if 0 then set _date_test;
if 0 then set _date_test(rename=(date=Prv_date value_01=prv_Value_01 Value_02=prv_Value_02));
 dcl hash H (dataset:'_date_test(rename=(date=Prv_date value_01=prv_Value_01 Value_02=prv_Value_02))') ;
   h.definekey  ("id",'Prv_date') ;
   h.definedata (all:'y') ;
   h.definedone () ;
end;
set _date_test;
by id;
if h.find(key:id,key: intnx('month',date,-1,'e'))=0 then output;
run;

[Kurt_Bremser]

If you have exactly one obs per id and month, then this will do it:

proc sort data=have;
by id date;
run;

data want;
set have;
by id;
format prv_date date9.;
prv_date = lag(date);
prv_value_01 = lag(value_01);
prv_value_02 = lag(value_02);
if first.id
then do;
  prv_date = .;
  prv_value_01 = .;
  prv_value_02 = .;
end;
run;

[MarkWik]

@Myurathan  You could mark any of the responses as an accepted solution and extend the courtesy to the respondents who volunteer for free to contribute in the community.  This is the very least we can do.