DATA Step, Macro, Functions and more

joining with previous month

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

joining with previous month

I have following table

 

01.png

 

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

 

02.png

 

Please help me.

 


Accepted Solutions
Solution
a week ago
Super User
Posts: 13,066

Re: joining with previous month

Posted in reply to Myurathan

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.

 

View solution in original post


All Replies
Solution
a week ago
Super User
Posts: 13,066

Re: joining with previous month

Posted in reply to Myurathan

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.

 

Super User
Posts: 22,853

Re: joining with previous month

Posted in reply to Myurathan

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

 

01.png

 

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

 

02.png

 

Please help me.

 


 

PROC Star
Posts: 1,340

Re: joining with previous month

Posted in reply to Myurathan

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

Occasional Contributor
Posts: 11

Re: joining with previous month

Posted in reply to 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 Star
Posts: 1,340

Re: joining with previous month

Posted in reply to 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
;

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;
Super User
Posts: 9,594

Re: joining with previous month

Posted in reply to Myurathan

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 99

Re: joining with previous month

Posted in reply to Myurathan

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 115 views
  • 7 likes
  • 6 in conversation