BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chemicalab
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

View solution in original post

5 REPLIES 5
chemicalab
Fluorite | Level 6

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

TomKari
Onyx | Level 15

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;

chemicalab
Fluorite | Level 6
Thank you very much for the reply
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
chemicalab
Fluorite | Level 6
works like a charm, thank you very much for this, date functions are always giving me a hard time

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1441 views
  • 0 likes
  • 3 in conversation