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

Hi,

 

Can someone help me with below query.I have dataset with 3 variables ID,VISIT,DATE...I would like to create new variable 'DAY'  with following criteria...the difference between two succesive dates will be number assigned to 'DAY'. The number in the fourth collumn is the expected result.

 

100010001           CYCLE 12014-08-05       1

100010001           CYCLE 1 2014-08-06       2

100010001           CYCLE 1 2014-08-07       3

100010001           CYCLE 1 2014-08-12     8

100010001           CYCLE 1 2014-08-19   15

100010001           CYCLE 2 2014-08-26    1

100010001           CYCLE 2 2014-08-27   2

100010001           CYCLE 2 2014-08-28  3

100010001           CYCLE 2 2014-09-02 8

100010001           CYCLE 2 2014-09-09 15

100010001           CYCLE 3 2014-09-16 1

100010001           CYCLE 3 2014-09-17 2

100010001           CYCLE 3 2014-09-18 3

100010001           CYCLE 3 2014-09-23 8

100010001           CYCLE 3 2014-09-30 15

100010001           CYCLE 4 2014-10-07 1

100010001           CYCLE 4 2014-10-08 2

100010001           CYCLE 4 2014-10-09 3

100010001           CYCLE 4 2014-10-14 8

100010001           CYCLE 4 2014-10-21 15

1 ACCEPTED SOLUTION

Accepted Solutions
mohamed_zaki
Barite | Level 11
data have;
format date YYMMDD10.;
input ID Visit $ Date YYMMDD10.;
cards;
100010001 CYCLE1 2014-08-05
100010001 CYCLE1 2014-08-06
100010001 CYCLE1 2014-08-07
100010001 CYCLE1 2014-08-12
100010001 CYCLE1 2014-08-19
100010001 CYCLE2 2014-08-26
100010001 CYCLE2 2014-08-27
100010001 CYCLE2 2014-08-28
100010001 CYCLE2 2014-09-02
100010001 CYCLE2 2014-09-09
100010001 CYCLE3 2014-09-16
100010001 CYCLE3 2014-09-17
100010001 CYCLE3 2014-09-18
100010001 CYCLE3 2014-09-23
100010001 CYCLE3 2014-09-30
100010001 CYCLE4 2014-10-07
100010001 CYCLE4 2014-10-08
100010001 CYCLE4 2014-10-09
100010001 CYCLE4 2014-10-14
100010001 CYCLE4 2014-10-21
;
run;

data want;
set have;
by ID VISIT;
retain Day;
lagdate=lag(Date);
if first.ID or first.visit then Day=1;
else Day=Day+intck('day',lagdate,Date);
drop lagdate;
run;

View solution in original post

4 REPLIES 4
Reeza
Super User

Your criteria does not match your output.

 

..the difference between two succesive dates

 

It looks like its the difference from the first date, not successive dates. Please clarify your requirements.

 

 

rakeshvvv
Quartz | Level 8
Yes Mam...Difference from first date for that particular id and visit....I may not explain correct but the expected output is the same...
Reeza
Super User

@rakeshvvv wrote:
Yes Mam...Difference from first date for that particular id and visit....I may not explain correct but the expected output is the same...

 But how do I know which is correct, the words or the data? 🙂

 

Here's another solution, 

 

data want;
set have;
by ID VISIT;
retain start_date;

if first.visit then start_date=date;

dur=date-start_date+1;

drop start_date;
run;
mohamed_zaki
Barite | Level 11
data have;
format date YYMMDD10.;
input ID Visit $ Date YYMMDD10.;
cards;
100010001 CYCLE1 2014-08-05
100010001 CYCLE1 2014-08-06
100010001 CYCLE1 2014-08-07
100010001 CYCLE1 2014-08-12
100010001 CYCLE1 2014-08-19
100010001 CYCLE2 2014-08-26
100010001 CYCLE2 2014-08-27
100010001 CYCLE2 2014-08-28
100010001 CYCLE2 2014-09-02
100010001 CYCLE2 2014-09-09
100010001 CYCLE3 2014-09-16
100010001 CYCLE3 2014-09-17
100010001 CYCLE3 2014-09-18
100010001 CYCLE3 2014-09-23
100010001 CYCLE3 2014-09-30
100010001 CYCLE4 2014-10-07
100010001 CYCLE4 2014-10-08
100010001 CYCLE4 2014-10-09
100010001 CYCLE4 2014-10-14
100010001 CYCLE4 2014-10-21
;
run;

data want;
set have;
by ID VISIT;
retain Day;
lagdate=lag(Date);
if first.ID or first.visit then Day=1;
else Day=Day+intck('day',lagdate,Date);
drop lagdate;
run;

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
  • 4 replies
  • 975 views
  • 0 likes
  • 3 in conversation