How create multiple records from horizontal data

[SASuserlot]

I have the following data, How I can create the multiple records based on the horizontal data , depening on the time variables. thank you for your help.

Assumptions:

1. if all four time vairables avaialble then need two records with startdate time and stopdate time depending the on the time

Example first  and second rows. only difference between first and second row is column D and E have same time

2. when "restarttime" missing consider the "intermediatestopTime as "restarttime"( and vice versa) and create two records.

3. when "intermediatestoptime" and "restartime" missing then create one record  for "stopdatetime" and "startdatetime".

data have;
    format date date9. starttime time8. intermediatestoptime time8. restarttime time8. finalstoptime time8.;
    do i = 1 to 4;
        usubjid = "00-01";
        date = '01OCT2024'd;
        /* Generate different times for each record */
        starttime = '12:20:00't + (i-1)*600; /* Increment start time by 10 minutes each iteration */
        intermediatestoptime = starttime + 600; /* Intermediate stop time is 10 minutes after start time */
        restarttime = intermediatestoptime + 600; /* Restart time is 10 minutes after intermediate stop time */
        finalstoptime = restarttime + 600; /* Final stop time is 10 minutes after restart time */

        output;
    end;
    drop i;

run;
data have1;
set have;
	if _n_ =2 then restarttime = '12:40:00't;
	if _n_ =3 then restarttime = '';
	if _n_ =4 then do; restarttime = '';intermediatestoptime='';end;

run;

[MarkusWeick]

Hi @SASuserlot , one aproach would be to split the data in two sets, one with the first start and stop time and one with the second start and stop time. And the append the two sets.

[MarkusWeick]

and here the corresponding code:

/*handling the missing*/
data want1;
	set have1;
	if intermediatestoptime = . then intermediatestoptime = restarttime;
	if restarttime = . then restarttime = intermediatestoptime;
run;

/*The two sets:*/
data want2a;
	keep date usubjid starttime finalstoptime;
	set want1;
	if intermediatestoptime ne missing then
		finalstoptime = intermediatestoptime;
run;

data want2b;
	keep date usubjid starttime finalstoptime;
	set want1;
	where intermediatestoptime is not missing;
	starttime = restarttime;
run;

/* appending the two sets*/
PROC APPEND BASE=want2a data=want2b;
quit;

/* Formating */
data want;
keep start_dttime stop_dttime usubjid;
retain start_dttime stop_dttime usubjid;
set want2a;
start_dttime = cat(put(date,DDMMYYD10.),"T",put(starttime,TOD.));
stop_dttime = cat(put(date,DDMMYYD10.),"T",put(finalstoptime,TOD.));
run;

[Patrick]

Assuming your last line in the desired result should show an end time of 13:20 and not 13:30 below should work.

data want;
  set have1;
  array dates {*} starttime intermediatestoptime restarttime finalstoptime;
  format start_dttime stop_dttime time8.;

  _n=n(of dates[*]);
  if _n=2 then
    do;
      start_dttime=starttime;
      stop_dttime =finalstoptime;
      output;
    end;
  else
    do;
      start_dttime=starttime;
      stop_dttime =intermediatestoptime;
      output;
      start_dttime=coalesce(restarttime,intermediatestoptime);;
      stop_dttime =finalstoptime;
      output;
    end;

  drop starttime intermediatestoptime restarttime finalstoptime _n;
run;

proc print data=want;
run;

[A_Kh]

data want (keep=usubjid startdtm stopdtm);
	set have1; 
	retain temp_end temp_start;
	if intermediatestoptime ne . and restarttime ne . then do;
		startdtm= catx('T', put(date, date11.), put(starttime, time8.));
		stopdtm= catx('T', put(date, date11.), put(intermediatestoptime, time8.));
		output;
		startdtm= catx('T', put(date, date11.), put(restarttime, time8.));
		stopdtm= catx('T', put(date, date11.), put(finalstoptime, time8.));
		output;
	end;
	if intermediatestoptime eq . and restarttime eq . then do;
		startdtm= catx('T', put(date, date11.), put(starttime, time8.));
		stopdtm= catx('T', put(date, date11.), put(finalstoptime, time8.));
		output;
	end; 
	if intermediatestoptime eq . and restarttime ne . then do;
		temp_end=restarttime;
		startdtm= catx('T', put(date, date11.), put(starttime, time8.));
		stopdtm= catx('T', put(date, date11.), put(temp_end, time8.));
		output;
		startdtm= catx('T', put(date, date11.), put(starttime, time8.));
		stopdtm= catx('T', put(date, date11.), put(finalstoptime, time8.));
		output;
	end; 
	if intermediatestoptime ne . and restarttime eq . then do;
		temp_start=intermediatestoptime;
		startdtm= catx('T', put(date, date11.), put(starttime, time8.));
		stopdtm= catx('T', put(date, date11.), put(intermediatestoptime, time8.));
		output;
		startdtm= catx('T', put(date, date11.), put(temp_start, time8.));
		stopdtm= catx('T', put(date, date11.), put(finalstoptime, time8.));
		output;
	end; 
run;

proc print data=want;
run;		

[Patrick]

@A_Kh I agree with you that adding the date component to the start and stop variables is a good idea. I wouldn't create a character variable though but just convert the SAS date to a SAS datetime value and then add it to the variables. 

Instead of...

startdtm= catx('T', put(date, date11.), put(starttime, time8.));

....consider using:

format startdtm e8601dt.;
startdtm=starttime + date*86400;

[Tom · Accepted solution]

SAS has a function for that 

startdtm=dhms(date,0,0,starttime);

And a FORMAT also

format startdtm E8601dt19.;

Example:

270  data _null_;
271    today=date();
272    time_of_day=time();
273    now=datetime();
274    now2 = dhms(today,0,0,time_of_day);
275    format today yymmdd10. time_of_day tod8. now: E8601dt19.;
276    put (_all_) (=/);
277  run;


today=2024-05-29
time_of_day=23:08:13
now=2024-05-29T23:08:13
now2=2024-05-29T23:08:13

[SASuserlot]

Thank you everyone, All of the responses worked for me, I choose the one which better fit to me. You guys are amazing