convert char time to SAS time format

[Didi_b]

I'm new to SAS and I'm looking to convert my data into SAS format

a sample

LineName Day hour call

x 05/01/2022 09H30 5

x 05/01/2022 09H45 3

x 05/01/2022 10H00 10

y 05/01/2022 09H30 1

y 05/01/2022 09H45 9

y 05/01/2022 10H00 7

.

.

.

x 31/01/2022 09H30 0

x 31/01/2022 09H45 9

.

.

.

 My day and hour format and informat are char. I want to have it in SAS time format.

I was able to format my var day into SAS format. but I couldn't do for my var hour.

Could someone help me please?

the proc content shows

var type len format informat

hour char 15 $15. $15.

[Reeza]

time = hms(input(substr(hour, 1,2), 8.), input(substr(hour, 4, 2),8.), 0);

---

@Didi_b wrote:

I'm new to SAS and I'm looking to convert my data into SAS format

a sample

LineName Day hour call

x 05/01/2022 09H30 5

x 05/01/2022 09H45 3

x 05/01/2022 10H00 10

y 05/01/2022 09H30 1

y 05/01/2022 09H45 9

y 05/01/2022 10H00 7

.

.

.

x 31/01/2022 09H30 0

x 31/01/2022 09H45 9

.

.

.

 My day and hour format and informat are char. I want to have it in SAS time format.

I was able to format my var day into SAS format. but I couldn't do for my var hour.

Could someone help me please?

the proc content shows

var type len format informat

hour char 15 $15. $15.

---

[Didi_b]

Thanks Reeza, but it doesn't work it gives the same output for every set of hour.
I get
hour Time
09H30 43260
09H45 43260
.
.
.
11H00 43260
11H15 43260

and when I format it gives me
time
12:01
12:01
.
.
.
12:01

[Reeza]

Works fine when tested with what you've posted.

However, glad @Tom solution worked for you already. 

data have;
infile cards truncover;
input LineName $ Day : mmddyy10. hour $ call;
time = hms(input(substr(hour, 1,2), 8.), input(substr(hour, 4, 2),8.), 0);
format time time.;
cards;
x 05/01/2022 09H30 5
x 05/01/2022 09H45 3
x 05/01/2022 10H00 10
y 05/01/2022 09H30 1
y 05/01/2022 09H45 9
y 05/01/2022 10H00 7
;;;;
run;

Results:

---

@Didi_b wrote:
Thanks Reeza, but it doesn't work it gives the same output for every set of hour.
I get
hour Time
09H30 43260
09H45 43260
.
.
.
11H00 43260
11H15 43260

and when I format it gives me
time
12:01
12:01
.
.
.
12:01

---

[Didi_b]

@Reeza thanks a lot for your input.
It works when I run the code as you shared.
The problem is that I don't know how to apply it to all my data table with 13 variables a multiples rows. But I've tried multiples ways

The sample I've shown is from my data table I import into SAS by
proc import
The thing is, I'm really new to SAS. There are things I don't understand yet.
I'm not familiar with the infile statement.

I add that to my program :
time = hms(input(substr(hour, 1,2), 8.), input(substr(hour, 4, 2),8.), 0);
format time time.; but it didn't work.

infile cards truncover; why cards truncover?

[Tom]

Ignore the INFILE and INPUT. Those are for reading data from a text file.  That was just included in the example so there was some actual data to test with.  If you are getting the data from an existing dataset with a SET statement you don't need them. 

Note if you are using PROC IMPORT to read a text file you can convert to writing your own data step to read the file instead.  Then you can avoid the issues caused by PROC IMPORT having the GUESS what the data is.  Then you would want to have INFILE and INPUT statements. 

The TRUNCOVER option is to keep SAS from running to the next line when it runs out of data to feed to the INPUT statement from the current line.  The default is FLOWOVER when jumps to the next when you run out of data.  There is also the MISSOVER option which you almost never want to use because it could cause some useful data to be skipped.  MISSOVER can be made to work like TRUNCOVER if you are careful to only use LIST MODE input style.

The CARDS keyword (also known as DATALINES) is to indicate that the file being read is the in-line data that follows the CARDS/DATALINE statement at the end of the data step.  Using this allows you to use INFILE options like TRUNCOVER when using in-line data lines.

[Didi_b]

Okay thanks a lot @Tom.
It is an unusual Excel file from which I use the proc import.
I'll try to post the program I used

[Tom]

If you have a real XLSX file then you won't have any flexibility in converting it into a SAS dataset.

But if you have a CSV file (which is just a text file and has nothing in particular to do with Excel) then you can read it directly.

[Didi_b]

@Tom @Reeza Sorry for posting a bit late

Here the program I use with @Tom Solution. I've tried to replace with @Reeza solution whithout succeding

proc import datafile="filepath\file_2022-01-12.xls" dbms=xls out=table_name replace;
sheet="sheetname";
namerow=7;
startrow=10;
Endrow=934;
getnames=yes;
run;

data test1;
set Work.table_name;

if Day="" then Day='12/01/2022';
if Day="Sous Total" then delete;
else Day='12/01/2022';
NJour=input(Day,anydtdte12.);

format NDay MMDDYY10.;
NHour=input(translate(Hour,':','H'),time15.);
format NHour tod5.;

/*time=hms(input(substr(Jour, 1,2),8.),input(substr(Jour, 4,2),8.), 0);
format time time.;*/

retain Line;
if not missing(LineName) then Line=LineName;
else LineName=Line;
drop Line Day Hour B D J P;

run;

[Kurt_Bremser]

Be careful with using the ANYDTDTE informat. Depending on locale, your 12/01/2022 can end up as 1st of December or 12th of January. Rather use DDMMYY or MMDDYY instead, to be on the safe side.

[Didi_b]

Okay thanks. I've tried it at the time but it didn't work that's why I used the anydate fonction. But today it did work with the MMDDYY10. fonction. Thanks

[Tom · Accepted solution]

So if HOUR is character with length of 15 (the format and/or informat attached play no role) then use INPUT() function to convert to time.  You will need to change the letter H into a : if you want to use the TIME informat.

Attach a time type FORMAT to your new numeric variable so it prints in a way that humans can understand.

data want;
  set have;
  time = input(translate(hour,':','H'),time15.);
  format time tod5.;
run;

[Didi_b]

Thanks Tom! it worked