BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SASuserlot
Barite | Level 11

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".

 

SASuserlot_0-1716949106735.png

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

7 REPLIES 7
MarkusWeick
Barite | Level 11

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.

Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
MarkusWeick
Barite | Level 11

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;
Please keep the community friendly.
Like posts you agree with or like. Mark helpful answers as “accepted solutions”. Generally have a look at https://communities.sas.com/t5/Getting-Started/tkb-p/community_articles
Patrick
Opal | Level 21

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
Lapis Lazuli | Level 10
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;		

Capture.PNG

Patrick
Opal | Level 21

@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
Super User Tom
Super User

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
Barite | Level 11

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

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 645 views
  • 9 likes
  • 5 in conversation