topic Re: how to read the 12 hour time and format it to 24 hour time? in SAS Data Management

how to read the 12 hour time and format it to 24 hour time?

xinyao2019 — Wed, 26 Jun 2019 16:39:22 GMT

Hi All,

In the csv file, time is coded as:

3:50:00 PM

11:16:00 AM

99:99

,,,

99:99 as unknow,

proc contents show the time variable : char, $ 21

proc print shows the time :

99:99

0.4618...

4.5138...

i need to time formatted to hhmm.

Could you please help to figure out the codes?

thanks

Re: how to read the 12 hour time and format it to 24 hour time?

Reeza — Wed, 26 Jun 2019 16:41:14 GMT

Go back to your import step and make sure to import it correctly, fixing it after the fact is more work and painful.

Re: how to read the 12 hour time and format it to 24 hour time?

ballardw — Wed, 26 Jun 2019 17:00:21 GMT

@xinyao2019 wrote:

Hi All,

In the csv file, time is coded as:

3:50:00 PM

11:16:00 AM

99:99

,,,

99:99 as unknow,

proc contents show the time variable : char, $ 21

proc print shows the time :

99:99

0.4618...

4.5138...

i need to time formatted to hhmm.

Could you please help to figure out the codes?

thanks

Show the code used to read the CSV file.

If there was a generated data step because you used Proc Import, show that as well.

Copy all of the code from the LOG including any messages. Paste into a code box opened on the forum using the {I} or "running man" icon to preserve the code and any diagnostic message format.

Re: how to read the 12 hour time and format it to 24 hour time?

xinyao2019 — Wed, 26 Jun 2019 18:33:54 GMT

i could not get the csv file read to sas. i saved it to xlsx and read it sas.

proc import OUT = a
datafile = 'b\predeathfull2019.xlsx'
DBMS = xlsx replace;
getnames=YES;
sheet = "deathfull2019pre";
run;

Re: how to read the 12 hour time and format it to 24 hour time?

Reeza — Wed, 26 Jun 2019 18:37:47 GMT

That's unlikely to fix your time issues. You'll still have to recode it especially because there isn't a way to control types from Excel.

Re: how to read the 12 hour time and format it to 24 hour time?

ballardw — Wed, 26 Jun 2019 20:11:07 GMT

@xinyao2019 wrote:

i could not get the csv file read to sas. i saved it to xlsx and read it sas.

proc import OUT = a
datafile = 'b\predeathfull2019.xlsx'
DBMS = xlsx replace;
getnames=YES;
sheet = "deathfull2019pre";
run;

If your data is not too sensitive perhaps you could paste a few lines of the data from the CSV file into a code box opened with the forum's {I} icon and the code you attempted.

An entirely too common problem we are seeing here is people attempting to read "CSV" and finding out is tab delimited or some other than comma delimited. If you use the wrong delimiter, or do not supply one with a CSV file, you can get all sorts of problems if the data is not actually comma separated or uses a different delimiter than supplied to proc import.

"i could not get the csv file read" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. 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.

Re: how to read the 12 hour time and format it to 24 hour time?

Reeza — Wed, 26 Jun 2019 20:16:46 GMT

I suspect this was the OP's previous question about PROC IMPORT.

Re: how to read the 12 hour time and format it to 24 hour time?

xinyao2019 — Wed, 26 Jun 2019 20:23:45 GMT

i did post the proc import CSV file problem.

https://communities.sas.com/t5/SAS-Programming/proc-import-csv-file-errors/td-p/569148/page/2

i cannot post any data here since they are sensitive.

i saved the csv to xlsx and read the xlsx file into sas without issue.

then i found the time variable became a problem.

do i have to read the CSV to fix the time issue?

thanks

Re: how to read the 12 hour time and format it to 24 hour time?

Tom — Wed, 26 Jun 2019 21:07:09 GMT

You cannot "save" a CSV to Excel. It is a text file. Most text editors do not write to Excel formats. Perhaps you mean that you allowed Excel to automatically open the CSV file and then saved (the possibly already corrupted) version using XLSX format?

Open Excel and then try to open the file from within Excel. Excel keeps changing the the interface over time so exactly what menu items to click on changes. But the goal is to get to the screen where it lets you define the types for the columns instead of Excel either guesisng or worse setting the type differently for every individual cell. You might even be able to get to that screen when auto opening the CSV file by clicking on something that shows up on the spreadsheet in Excel.

Because of the bogus 99:99 value in some of the cells you will probably want to tell Excel that the column is text and not time.

Then when you get it into SAS use the INPUT() function to convert the strings into time values.

Re: how to read the 12 hour time and format it to 24 hour time?

Reeza — Wed, 26 Jun 2019 21:06:24 GMT

>without issue
No, it just hides the issues for you to deal with later, like here.

Re: how to read the 12 hour time and format it to 24 hour time?

ballardw — Wed, 26 Jun 2019 21:09:20 GMT

@xinyao2019 wrote:

i did post the proc import CSV file problem.

https://communities.sas.com/t5/SAS-Programming/proc-import-csv-file-errors/td-p/569148/page/2

i cannot post any data here since they are sensitive.

i saved the csv to xlsx and read the xlsx file into sas without issue.

then i found the time variable became a problem.

do i have to read the CSV to fix the time issue?

thanks

No but you will have to do some additional coding because you apparently have text values and that may be fun as you will need to use some different formats to read this as the layout is changing.

Also, is 99:99 actually an indicator for not collected data? That is a very suspect "time" value as minutes seldom go past 59 (60=> a new hour. So this is one example of reading the 3 provided "times" into actual SAS time values:

data junk;
   infile datalines truncover dlm=',';
   informat x $12.;
   input x;
   if length(x)> 5 then y=input(x,time12.);
   else y=.; /*assumes 99:99 is not actual time*/
   format y timeampm12.;

datalines;
3:50:00 PM
11:16:00 AM
99:99
;

If you don't do anything special for 99:99 it will likely be treated as 4:39:00 AM. If you don't believe me run

data junk;
   infile datalines truncover dlm=',';
   informat x $12.;
   input x;
   if length(x)> 5 then y=input(x,time12.);
   else y= input(x,time.); 
   format y timeampm12.;

datalines;
3:50:00 PM
11:16:00 AM
99:99
;

The time12. format needed to read the longer values with AM PM will not read 99:99. So the alternate line is needed with a different length informat.

Re: how to read the 12 hour time and format it to 24 hour time?

Tom — Wed, 26 Jun 2019 21:25:39 GMT

Remove the embedded linefeeds in your original file so that it can be read as a CSV file by SAS.

See this discussion and solution.

https://communities.sas.com/t5/SAS-Data-Management/Special-Carriages/m-p/214770/highlight/true#M5143

data _null_;
  infile 'sample_issues.csv' recfm=n ;
  file 'sample_issues_fixed.csv' recfm=n ;
  input x $char1. ;
  quotes+(x='"');
  if mod(quotes,2) and (x='0D'x or x='0A'x) then put '|';
  else put x $char1.;
run;

Now you can try using PROC IMPORT to read the new file.

Or write your own data step to read it. For you TIME variable you could create your own informat or if you know that just the 99:99 values are the only invalid values you can use the ?? modifier in the INPUT statement to suppress the error messages they will call. SAS will set the values to missing.

Re: how to read the 12 hour time and format it to 24 hour time?

xinyao2019 — Wed, 26 Jun 2019 21:51:20 GMT

thank you so much!!!

Re: how to read the 12 hour time and format it to 24 hour time?

xinyao2019 — Wed, 26 Jun 2019 23:12:55 GMT

yes, yo are right. the 99:99 is time unknown. after i format the time it changed to 100

i read the csv file into sas now.

time is numerical.

i ran the codes below:

data test2;
set z5;
format TIME_OF_INJURY hhmm5.;

run;

got :

time

100

11:05

100

4:00

100

16:00

...

how to change the time from hh:mm to hhmm ?

thanks for the help!