Hi all,
I have following column date with this format (datetime) 20JUL16:16:39:12 and i am trying to first get the max date of it per ID and then i want to calculate the difference in weeks between each date and the max date, how can i do that?
Thank you in advance
First thing you would do is to find the max(date) then merge that to the original date, then do an intck on the two, something like (and you have not posted any test data in the form of a datastep or required output, so this is just concept):
proc sql; create table WANT as select A.*, B.DTE, intck("week",A.DATE,B.DTE) as WEEKS from HAVE A left join (select max(DATE) as DTE from HAVE group by ID) B on A.ID=B.ID; quit;
Hi all,
I have following column date with this format (datetime) 20JUL16:16:39:12 and i am trying to first get the max date of it per ID and then i want to calculate the difference in weeks between each date and the max date, how can i do that?
Thank you in advance
This should get you much of the way there. Note that there are many, many options for the intck function in terms of how you want the difference calculated, so adjust as necessary.
Tom
data have;
input ID DT datetime.;
format DT datetime.;
cards;
1 01Jan2016:00:00:00
1 01Feb2016:00:00:00
2 01Apr2016:00:00:00
2 01Mar2016:00:00:00
run;
proc means noprint nway;
class ID;
var DT;
output out=inter1(drop=_TYPE_ _FREQ_) max()=;
run;
data datecomp;
input ID DT2 datetime.;
format DT2 datetime.;
cards;
1 01Jan2016:00:00:00
1 06Jan2016:00:00:00
1 11Jan2016:00:00:00
1 16Jan2016:00:00:00
1 21Jan2016:00:00:00
1 26Jan2016:00:00:00
1 31Jan2016:00:00:00
1 05Feb2016:00:00:00
1 10Feb2016:00:00:00
1 15Feb2016:00:00:00
1 20Feb2016:00:00:00
1 25Feb2016:00:00:00
1 01Mar2016:00:00:00
1 06Mar2016:00:00:00
1 11Mar2016:00:00:00
1 16Mar2016:00:00:00
1 21Mar2016:00:00:00
1 26Mar2016:00:00:00
1 31Mar2016:00:00:00
1 05Apr2016:00:00:00
1 10Apr2016:00:00:00
1 15Apr2016:00:00:00
1 20Apr2016:00:00:00
1 25Apr2016:00:00:00
2 07Jul2016:00:00:00
run;
proc sql noprint;
create table want as
select i.ID,
i.DT,
d.DT2,
intck('dtweek',DT2,DT) as Diff
from inter1 i inner join datecomp d on(i.ID = d.ID);
quit;
First thing you would do is to find the max(date) then merge that to the original date, then do an intck on the two, something like (and you have not posted any test data in the form of a datastep or required output, so this is just concept):
proc sql; create table WANT as select A.*, B.DTE, intck("week",A.DATE,B.DTE) as WEEKS from HAVE A left join (select max(DATE) as DTE from HAVE group by ID) B on A.ID=B.ID; quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.