Help using Base SAS procedures

difference in weeks sas datetime

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 126
Accepted Solution

difference in weeks sas datetime

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


Accepted Solutions
Solution
‎08-09-2016 09:49 AM
Super User
Super User
Posts: 7,991

Re: difference in weeks sas datetime

Posted in reply to chemicalab

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


All Replies
Frequent Contributor
Posts: 126

week difference in datetime format

Posted in reply to chemicalab

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

PROC Star
Posts: 1,167

Re: week difference in datetime format

Posted in reply to chemicalab

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;

Frequent Contributor
Posts: 126

Re: week difference in datetime format

Thank you very much for the reply
Solution
‎08-09-2016 09:49 AM
Super User
Super User
Posts: 7,991

Re: difference in weeks sas datetime

Posted in reply to chemicalab

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;
Frequent Contributor
Posts: 126

Re: difference in weeks sas datetime

works like a charm, thank you very much for this, date functions are always giving me a hard time
☑ This topic is solved.

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

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