date contraints with adding dates

[monday89]

Hi Currently working on the data set that i received help from forum members before but I have to add more constraints.

Let's say the current dataset is the following:

subject	day	start_date	stop_date	value	value1
1	1	1/2/2019 14:34	1/2/2019 19:32	23	11
1	2	1/3/2019 19:33	1/3/2019 23:44	21	23
1	3	1/4/2019 21:49	1/7/2019 14:10	41	99
2	1	2/3/2019 21:33	2/3/2019 23:44	23	65
2	2	2/4/2019 23:33	2/5/2019 1:44	12	23

If start and stop date is not the same (meaning it carries over the next day), i would like to assign a midnight cut off (and it works), but I need the following date to carry over as needed with appropriate value and value1 being status

Something like this below:

subject	start_date_time	stop_date_time	new_stop_date_time	value	value1
1	1/2/2019 14:34	1/2/2019 19:32	1/2/2019 19:32	23	11
1	1/3/2019 19:33	1/3/2019 23:44	1/3/2019 23:44	21	23
1	1/4/2019 21:49	1/7/2019 14:10	1/4/2019 23:59	41	99
1	1/5/2019 0:00		1/5/2019 23:59	41	99
1	1/6/2019 0:00		1/6/2019 23:59	41	99
1	1/7/2019 0:00		1/7/2019 14:10	41	99
2	2/3/2019 21:33	2/3/2019 23:44	2/3/2019 23:44	23	65
2	2/4/2019 23:33	2/5/2019 1:44	2/4/2019 23:59	12	23

I broke date/time into date formats only first

then did the following

if (stop date> start date) then new_stop_date = dhms(startdate, 23,59,59)

else new_stop_date = stop_date

but I am stuck on how to carry over the same values from that same field AND adding new start date as needed

Any help would be appreciated

[ballardw]

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[PaigeMiller]

---

@monday89 wrote:

but I am stuck on how to carry over the same values from that same field AND adding new start date as needed

---

Show us what this means.

And follow the link from @ballardw regarding providing data in the desired format.

[monday89]

It was something like for carrying over the values but not that filling the dates +1 

data a.want; set have;

by subject;

if first.subject then new_value = value;

else new_value = coalesce (value, new_value); retain new_value;

run;

[Tom]

First let's convert your printout into an actual dataset.

data have ;
  input subject day start_date start_time stop_date stop_time value value1 ;
  informat start_date stop_date mmddyy. start_time stop_time time.;
  start_dt = dhms(start_date,0,0,start_time);
  stop_dt = dhms(stop_date,0,0,stop_time);
  format start_dt stop_dt datetime20.;
  drop start_date start_time stop_date stop_time ;
cards;
1 1 1/2/2019 14:34 1/2/2019 19:32 23 11
1 2 1/3/2019 19:33 1/3/2019 23:44 21 23
1 3 1/4/2019 21:49 1/7/2019 14:10 41 99
2 1 2/3/2019 21:33 2/3/2019 23:44 23 65
2 2 2/4/2019 23:33 2/5/2019 1:44 12 23
;

Now let's make a program to split the overnight stays.

data want ;
  set have ;
  format start_new stop_new datetime. ;
  intervals=intck('dtday',start_dt,stop_dt);
  do offset=0 to intervals;
    if offset=0 then start_new=start_dt;
    else start_new=intnx('dtday',start_dt,offset,'b');
    if offset=intervals then stop_new=stop_dt;
    else stop_new=intnx('dtday',start_new,0,'e');
    output;
  end;
run;

Results:

You might want to drop the original dates and rename the new dates. And perhaps drop the other two variables.

[monday89]

Thank you! I tried it but it showing me: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY

       expression is missing, zero, or invalid" 

I added "by" subject ID after "do", it wont work 😞