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

[v_dangkh]

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:

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

[dcruik]

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?

[v_dangkh]

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. 

[Patrick]

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.

[v_dangkh]

Thanks,

I use SAS 9.3

[FreelanceReinh · Accepted solution]

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

[v_dangkh]

Thanks so much @FreelanceReinh @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;