## Date-Time Format as 09/01/2015 0:04

Solved
Occasional Contributor
Posts: 6

# Date-Time Format as 09/01/2015 0:04

Hello SAS Community,

I need help formating SAS to read the date properly from an excel sheet in order to answer the following questions:

1. Average Start and End time in hours?
2. What day of the week shows up the most frequent?

I assume that once SAS has the format right, i can extract the date or time from the observations to find the average.

I very much appreciate your help.

Student,

DATA&colon;

Under the Starttime (B) and Stoptime(C) column on excel, the date format is displayed as:

 09/01/2015 0:04

Imported Excel to SAS using:

Proc import out=citi.sept15
datafile="C:\445_695\Course_data\201509-citibike-tripdata.csv"
DBMS=csv replace;
getnames = yes;
run;

SAS Proc Content:

Alphabetic List of Variables and Attributes # Variable Type Len Format Informat 12 14 8 10 11 9 15 4 6 7 5 2 3 1 13

 bikeid Char 7 \$7. \$7. birth_year Char 6 \$6. \$6. end_station_id Char 6 \$6. \$6. end_station_latitude Char 13 \$13. \$13. end_station_longitude Char 14 \$14. \$14. end_station_name Char 31 \$31. \$31. gender Char 3 \$3. \$3. start_station_id Char 6 \$6. \$6. start_station_latitude Char 20 \$20. \$20. start_station_longitude Char 20 \$20. \$20. start_station_name Char 31 \$31. \$31. starttime Char 19 \$19. \$19. stoptime Char 19 \$19. \$19. tripduration Char 6 \$6. \$6. usertype Char 12 \$12. \$12.

SAS output for Starttime:
9/1/2015 00:00:00

Accepted Solutions
Solution
‎11-30-2015 06:36 PM
Posts: 1,252

## Re: Date-Time Format as 09/01/2015 0:04

I fully support @dcruik's and @Patrick's recommendation to read the file using a data step. If columns starttime and stoptime have values like "9/1/2015 00:04:00" in the .csv file (as displayed in Notepad) -- and this seems to be the case according to your example "SAS Output" --, either MDYAMPM. or ANYDTDTM. should be a suitable informat -- depending on whether the date part of that datetime value means September 1st or January 9th, 2015.

Alternatively, you could "convert" those variables in CITI.SEPT15 which are actually numeric (incl. datetime variables) into numeric variables by means of the INPUT function and suitable informats (in a data step).

Indeed, once you have SAS datetime values, you can extract date and time using the DATEPART and TIMEPART functions, respectively, and get the day of the week by means of the WEEKDAY function (applied to the date part).

All Replies
Frequent Contributor
Posts: 132

## Re: Date-Time Format as 09/01/2015 0:04

[ Edited ]

Since the file is a Comma Separated Values file, I would recommend importing the data using a Data Step instead of the Import Procedure.  This will allow you to import the variables as whatever formats you want to import them as and display them as in SAS with your informat and format statements.  Using the Import Procedure, SAS basically scans the first few rows of each variable and guesses on the format to use.  Also, do you need to have the file imported the exact way it currently is, or could you modify the CSV file by creating some additional columns and such?

Occasional Contributor
Posts: 6

## Re: Date-Time Format as 09/01/2015 0:04

Hi dcruik,

Thank you for the quick reply, it is a SAS project so i assume i wont be able to manipulate the excel sheet. I will try to import it using DATA step however i am still clueless about the date and time format.

Posts: 4,736

## Re: Date-Time Format as 09/01/2015 0:04

You are NOT importing an Excel but a CSV - which is a comma separated text file. Don't get confused that .csv are normally open with Excel. Open the .csv with Notepad or some other text editor. That's the data you're dealing with and how SAS reads them.

So: You don't need to manipulate your data source but you can tell SAS how to read your data source and map it into SAS variables.

Proc Import does a lot for you but a SAS data step with an input statement gives you more flexibility if Proc Import doesn't get it right.

What are you using as client? SAS EG or SAS Studio?

With SAS EG there is an import wizard which would help you generate the data step - and then you modify the INFORMAT for the columns where you want to read the data differently.

Occasional Contributor
Posts: 6

## Re: Date-Time Format as 09/01/2015 0:04

Thanks,

I use SAS 9.3

Solution
‎11-30-2015 06:36 PM
Posts: 1,252

## Re: Date-Time Format as 09/01/2015 0:04

I fully support @dcruik's and @Patrick's recommendation to read the file using a data step. If columns starttime and stoptime have values like "9/1/2015 00:04:00" in the .csv file (as displayed in Notepad) -- and this seems to be the case according to your example "SAS Output" --, either MDYAMPM. or ANYDTDTM. should be a suitable informat -- depending on whether the date part of that datetime value means September 1st or January 9th, 2015.

Alternatively, you could "convert" those variables in CITI.SEPT15 which are actually numeric (incl. datetime variables) into numeric variables by means of the INPUT function and suitable informats (in a data step).

Indeed, once you have SAS datetime values, you can extract date and time using the DATEPART and TIMEPART functions, respectively, and get the day of the week by means of the WEEKDAY function (applied to the date part).

Occasional Contributor
Posts: 6

## Re: Date-Time Format as 09/01/2015 0:04

Thanks so much @FreelanceReinhard @Patrick @dcruik,

I got it to work, here is the code if you are were wondering

libname citi 'C:\445_695';
Data citi.sept15;
infile "C:\445_695\Course_data\201509-citibike-tripdata.csv" DLM = "," DSD firstobs=2;
input
tripduration
starttime :ANYDTDTM.
stoptime :ANYDTDTM.
start_station_id
start_station_name :\$31.
start_station_latitude
start_station_longitude
end_station_id
end_station_name :\$31.
end_station_latitude
end_station_longitude
bikeid
usertype :\$12.
birth_year
gender
;
format
starttime datetime19.
stoptime datetime19.
;
run;
proc print data = citi.sept15 (obs=100);
run;
🔒 This topic is solved and locked.