concatenating military time and date

[ksalottolo]

Hello,

I am working with hospital arrival dates and times and procedure dates and times. The end goal is to determine the amount of time elapsed from arrival to procedure. All of my dates are formatted as date9. and all of my times are formatted as time8. but they are MILITARY TIME.

I have so far concatenated the date and time columns to a datetime variable using the code below.

attrib arrivaldatetime format=datetime19.;

arrivaldatetime=input(put(HOSPARRIVALDATE,date9.)||put(HOSPARRIVALTIME,time8.),datetime.);

However, because my times are miliatry time, it is only creating the datetime variable for times that are before 13:00. Its as if SAS thinks a time such as 20:50 is not a real time.  

After much online searching, I have been unable to find a way to concatenate dates and military times. Any suggested code will be much appreciated!

Where the times are before 13:00 I am able to determine elapsed time btween arrival and procedure.

thanks

[Reeza]

It may be better to use DHMS() function to build your date time variable.

Can you please post sample data, SAS shouldn't have issues with 24 hour times, which is what I'm assuming is "Military" time. 

[ksalottolo]

Yes, i've attached a sample file. 

You can see the data i'm working from in columns labeled:

HOSPARRIVALTIME

HOSPARRIVALDATE

arrivaldatetime

arrivaldatetime only concatenates when hosparrivaltime is < 13:00.

The bloodproduct and headct columns with missing data are due to non-concatenation of times > 13:00 (done in an earlier step with the previously mentioned sas code).

[ballardw]

Note about Circular references in formula makes it likely there are multiple issues with the data in this format.

Could you post the data as a text file? Save the file as CSV and name with a TXT extension before posting to the forum.

Please show the entire SAS code you have used.

If you are trying to combine stuff in Excel then it is not a SAS question.

The values I see in the spreadsheet and the message about circular reference makes me believe this is not the actual in SAS.

[ksalottolo]

This is all done in SAS, not excel. Here is the full sas code. Very simple, i'm importing 6 files (I hid the file name), merging 3 'unique' files and 3 'propogated' files across study years, identifying the first procedure (either head CT or blood product), merging, then trying to get time elapsed. The attached file is after the merged step.

%macro import (sheet =, year = , DATAFILE=);

PROC IMPORT OUT=  HS.import_&year._&sheet

            DATAFILE= &DATAFILE

            DBMS=XLSX REPLACE;

     SHEET = &sheet;

RUN;

%mend import;

%import (sheet = unique, year = 2013, DATAFILE = "(hidden)");

%import (sheet = unique, year = 2014, DATAFILE = "(hidden)");

%import (sheet = unique, year = 2015, DATAFILE = "(hidden)");

%import (sheet = prop, year = 2013, DATAFILE = "(hidden)");

%import (sheet = prop, year = 2014, DATAFILE = "(hidden)");

%import (sheet = prop, year = 2015, DATAFILE = "(hidden)");

Data hs.propogated;

Set import_2013_prop import_2014_prop import_2015_prop;

attrib procdatetime format=datetime19.;

procdatetime=input(put(proc_start_date,date9.)||put(proc_start_time,time8.),datetime.);

run;

proc sort data=hs.propogated;

by registry_ procedure descending procdatetime ;

run;

Data hs.coded_propogated;

Set hs.propogated;

by REGISTRY_;

KEEP REGISTRY_   first_headCT first_blood ;

RETAIN  first_headCT first_blood ;

IF FIRST.REGISTRY_ THEN do;  first_headCT = .;  first_blood = .;  END;

if procedure = 'BLOOD' and procdatetime NE . then first_blood=procdatetime;

if procedure = 'HEADCT' and procdatetime NE . then first_headct=procdatetime;

format first_headct first_blood datetime19.;

IF LAST.REGISTRY_ THEN OUTPUT;

Run;

data hs.coded_unique;

set import_2013_unique import_2014_unique import_2015_unique;

attrib arrivaldatetime format=datetime19.;

arrivaldatetime=input(put(HOSPARRIVALDATE,date9.)||put(HOSPARRIVALTIME,time8.),datetime.);

run;

proc sort data=hs.coded_propogated; by registry_; run;

proc sort data=hs.coded_unique; by registry_; run;

data hs.merged;

merge hs.coded_propogated hs.coded_unique;

by registry_;

arrival_to_ct =  first_headCT - arrivaldatetime;

ct_to_blood = first_headCT - first_blood ;

format arrival_to_ct ct_to_blood hhmm.;

run;

data merged;

set hs.merged (keep = registry_ first_headCT first_blood hosparrivaltime hosparrivaldate arrivaldatetime arrival_to_ct ct_to_blood);

run;

PROC EXPORT DATA= WORK.MERGED

            OUTFILE= "\\vmware-host\Shared Folders\Desktop\merged.csv"

            DBMS=CSV LABEL REPLACE;

     PUTNAMES=YES;

RUN;

[PGStats · Accepted solution]

Better use

attrib arrivaldatetime format=datetime19.;
arrivaldatetime = dhms(HOSPARRIVALDATE, hour(HOSPARRIVALTIME), minute(HOSPARRIVALTIME), second(HOSPARRIVALTIME));

your code was missing a separator between date and time.

[ksalottolo]

so simple- thanks for the fix!