DATA Step, Macro, Functions and more

Gap of at least 30 days

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Gap of at least 30 days

PersonChocolateDate ReceivedDate FinishedNo chocolateActual No chocolate
1Snickers7-May-1511-May-1500
1MM8-May-1513-May-1500
1Twix10-May-1518-May-1500
1Bounty12-May-1516-May-1500
1Hersheys17-Jun-1521-Jun-1510

 

I need to find when there were at least 30 days without a chocolate for person 1. My current code sorts the data by date received, and gets me the solution to "No chocolate". This is wrong, as it marks the last row as 1, suggesting a difference between (date finsihed - date received for the next chocolate) is 16May15 - 17jun15 = 31 days, however the code does not account for the Bounty choclate date finished which is 18May15, and hence the difference should have been 18May15 - 17Jun15 = 29days and hence does not meet the criteria of at least 30 days as suggested by the Actual No Chocolate column.

 

Sorting the data by date finshed descending does not help either

 

Any help would be appreciated thanks

 

Code

 

data want;

set have

by person date_received;

gap=date_received-lag(date_finished);

if first.person=1 then no_chocolate=1;

else if gap>=30 then no_chocolate+1;

run;


Accepted Solutions
Solution
‎01-24-2017 03:55 PM
Super User
Posts: 6,947

Re: Gap of at least 30 days

data have;
infile cards missover;
input Person	Chocolate$	Date_Received:date9.	Date_Finished:date9.;
format date: date9.;
cards;
1 Snickers 7-May-15 11-May-15
1 MM 8-May-15 13-May-15
1 Twix 10-May-15 18-May-15
1 Bounty 12-May-15 16-May-15
1 Hersheys 17-Jun-15 21-Jun-15
1 Milka 31-aug-15 05-sep-15
;
run;

data want;
set have;
by person;
retain max_finish;
no_chocolate = 0;
if not first.person
then do;
  if date_received - max_finish > 30
  then no_chocolate = 1;
end;
max_finish = max(max_finish,date_finished);
run;

I added an additional data line to make sure that a value of 1 for no_chocolate is triggered.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
PROC Star
Posts: 552

Re: Gap of at least 30 days

I dont see that the Date finished for the Bounty is 18May15? Isn't that the date finished for the Twix?

Occasional Contributor
Posts: 17

Re: Gap of at least 30 days

Yes, it should have been the finished date for Twix.

Thanks for pointing that out.

Super Contributor
Posts: 474

Re: Gap of at least 30 days

Hi.

 

Your code should work, Date Finished of Bounty is 16-May-2015.

 

data want;

set have

by person date_received;

gap=date_received-lag(date_finished);

if first.person=1 then no_chocolate=1;

else if gap>=30 then no_chocolate+1;

run;

 

Bear in mind that if you want to count exclusively the days between to dates you need to adjust by subtracting one day:

 

For example, if date_received=17-may-2015 and date_finished=16-may-2015, former minus the latter would return 1 day, is that the expected result? maybe you are looking for something like this:

 

gap=date_received-lag(date_finished)-1;

 

Hope it helps.

 

Daniel Santos @ www.cgd.pt;

Occasional Contributor
Posts: 17

Re: Gap of at least 30 days

The finished date for Twix is what I was looking for which is 18May, and it is here that the code collapses
Super User
Posts: 6,947

Re: Gap of at least 30 days

Run my code. If it doesn't do what you want, post the modified example data step, and where the result differs from your expectations.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,131

Re: Gap of at least 30 days

please try the sql code,

 

data have;
infile cards missover;
input Person	Chocolate$	Date_Received:date9.	Date_Finished:date9.;
format date: date9.;
cards;
1	Snickers	7-May-15	11-May-15	
1	MM	        8-May-15	13-May-15	
1	Twix	    10-May-15	18-May-15	
1	Bounty	    12-May-15	16-May-15	
1	Hersheys	17-Jun-15	21-Jun-15	
;

proc sql;
create table test as select a.*, b.Date_Received as other_rec, b.Date_Finished as other_fini,a.Date_Received-b.Date_Finished as day, b.Chocolate as choc from have as a, 
(select Chocolate,person, Date_Received, Date_Finished from have) as b where a.person=b.person;
quit;
Thanks,
Jag
Solution
‎01-24-2017 03:55 PM
Super User
Posts: 6,947

Re: Gap of at least 30 days

data have;
infile cards missover;
input Person	Chocolate$	Date_Received:date9.	Date_Finished:date9.;
format date: date9.;
cards;
1 Snickers 7-May-15 11-May-15
1 MM 8-May-15 13-May-15
1 Twix 10-May-15 18-May-15
1 Bounty 12-May-15 16-May-15
1 Hersheys 17-Jun-15 21-Jun-15
1 Milka 31-aug-15 05-sep-15
;
run;

data want;
set have;
by person;
retain max_finish;
no_chocolate = 0;
if not first.person
then do;
  if date_received - max_finish > 30
  then no_chocolate = 1;
end;
max_finish = max(max_finish,date_finished);
run;

I added an additional data line to make sure that a value of 1 for no_chocolate is triggered.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 179 views
  • 0 likes
  • 5 in conversation