topic Re: joining with previous month in SAS Programming

joining with previous month

Myurathan — Mon, 14 May 2018 15:20:49 GMT

I have following table

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

Please help me.

Re: joining with previous month

ballardw — Mon, 14 May 2018 15:28:12 GMT

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.

Re: joining with previous month

Reeza — Mon, 14 May 2018 15:30:22 GMT

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.

Re: joining with previous month

novinosrin — Mon, 14 May 2018 15:40:49 GMT

@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!

Re: joining with previous month

Myurathan — Mon, 14 May 2018 15:43:32 GMT

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
;

Re: joining with previous month

novinosrin — Mon, 14 May 2018 16:37:19 GMT

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;

Re: joining with previous month

Kurt_Bremser — Mon, 14 May 2018 18:21:24 GMT

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;

Re: joining with previous month

MarkWik — Tue, 15 May 2018 14:51:30 GMT

@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.