I have following table
I want to have previous month data joined to the table. something like this.
Please help me.
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.
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.
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.
@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!
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;
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;
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: