topic codes not working to extract separate columns of dates and time from one column. in SAS Programming

codes not working to extract separate columns of dates and time from one column.

Fatima123456 — Thu, 31 Aug 2023 11:07:07 GMT

I have a big data set consists of two columns, its sample is following:

timestamp users

2/05/2018 4:53 150785

2/05/2018 6:38 150785

3/05/2018 0:35 145510

3/05/2018 13:43 145511

Now I nee two separate columns of date and time. For that purpose I am using following code:

data amz_processed;
set amz;
date = datepart(timestamp);
time = timepart(timestamp);

format date yymmdd10. time timeampm.;

drop timestamp;
run;

but in output file datepart and timepart columns are empty with log error in log file: Invalid numeric data. Please correct me where I am doing wrong.

Thanks

Re: codes not working to extract separate columns of dates and time from one column.

Patrick — Thu, 31 Aug 2023 11:18:43 GMT

Is your timestamp variable numeric and contains a SAS Datetime value?

Please run below code and let us know.

proc contents data=<libref>.<table>(keep=timestamp);
run;

proc print data=<libref>.<table>(keep=timestamp obs=1);
run;

Re: codes not working to extract separate columns of dates and time from one column.

ballardw — Thu, 31 Aug 2023 15:23:20 GMT

@Fatima123456 wrote:

but in output file datepart and timepart columns are empty with log error in log file: Invalid numeric data. Please correct me where I am doing wrong.

Thanks

ANY time you have an error and want help addressing it then provide the log for the step that creates the error. Copy the text of the code and all notes, warnings and error messages. On the forum open a text box using the </> icon above the message window. Paste the text into the box.

The text box is important because it will preserve formatting of text including the diagnostic information SAS often provides that will be stripped out when pasted in to the main message window. The box also serves to separate the code and messages more clearly from discussion.

Your particular error means that I would place a fair sized wager that your "timestamp" is a character variable and not a SAS datetime value that the DATEPART and TIMEPART functions require.

A second strong clue is the values you show for "timestamp" are not in any likely SAS format unless someone in your organization has spend some time creating a custom format that is not-well-though-out (in my opinion). Why 2/ (one digit) and then 05/ (two digits) for example is pretty odd mixture.