topic have a question regarding date time in sas in SAS Programming

have a question regarding date time in sas

meetagupta — Tue, 01 Oct 2019 18:16:57 GMT

I have a SAS data set and the time&date column have vales like 42107.66744212963, 42109.458969907406 etc.

How can I convert these so that I can find out in what month the sale were the highest? Thanks.

Re: have a question regarding date time in sas

data_null__ — Tue, 01 Oct 2019 18:33:59 GMT

@meetagupta wrote:

I have a SAS data set and the time&date column have vales like 42107.66744212963, 42109.458969907406 etc.

How can I convert these so that I can find out in what month the sale were the highest? Thanks.

looks like the time-part is decimal. You can convert to SAS date-time using DHMS function.

data _null_;
   input x;
   datetime = dhms(x,0,0,0);
   put x= datetime=datetime22.;
   cards;
42107.66744212963
42109.458969907406
;;;;

Do you know what dates those values should be. They may need to be adjusted if the are UNIX dates or WINDOWS. As you can see these years do look quite right.

x=42107.667442 datetime=14APR2075:16:01:07
x=42109.45897 datetime=16APR2075:11:00:55

Re: have a question regarding date time in sas

PaigeMiller — Tue, 01 Oct 2019 18:28:10 GMT

Unless you are expecting a date/time value on January 1, 1960, these are not SAS date/time values.

Unless you are expecting a date in 2075, these are not SAS date values.

Sometimes, transferring these values from other software (perhaps Excel?) produces date/time values that don't work in SAS.

To see the date/time, this code will work

data _null_;
	y=42107.66744212963;
	put y datetime18.;
run;

Re: have a question regarding date time in sas

ballardw — Tue, 01 Oct 2019 18:31:25 GMT

@meetagupta wrote:

I have a SAS data set and the time&date column have vales like 42107.66744212963, 42109.458969907406 etc.

How can I convert these so that I can find out in what month the sale were the highest? Thanks.

My guess is this data started in Excel. If the first value is supposed to be around 15 Apr 2015 then my guess is pretty good.

I might suggest going back to the step where you brought the data into SAS and show us that. Likely something in the process missed that was supposed to be a date and time value.

Re: have a question regarding date time in sas

meetagupta — Tue, 01 Oct 2019 18:37:11 GMT

Customer ID	Time stamp	Product Name
147	4/13/15 16:01	Cricket Bat MRF
151	4/15/15 11:00	Badminton Racket Yonex

This is an example of the excel file and I wrote the code to import it into SAS.

FILENAME REFFILE '/home/u42075552/sasuser.v94/Transactional_data.xlsx';

PROC IMPORT DATAFILE=REFFILE
DBMS=XLSX
OUT=WORK.IMPORT2;
GETNAMES=YES;
SHEET="Input";
RUN;

PROC SQL;
CREATE TABLE task1.query AS
SELECT customer_id, time_stamp , product_name
FROM WORK.IMPORT2;
RUN;
QUIT;

Re: have a question regarding date time in sas

data_null__ — Tue, 01 Oct 2019 18:50:00 GMT

Based on the extra info you provided.

106  data _null_;
107     input x;
108     adj = sum(x,'30dec1899'd);
109     datetime = dhms(adj,0,0,0);
110     put x= adj= datetime=datetime22.;
111     cards;

x=42107.667442 adj=20191.667442 datetime=13APR2015:16:01:07
x=42109.45897 adj=20193.45897 datetime=15APR2015:11:00:55

Re: have a question regarding date time in sas

ChrisNZ — Tue, 01 Oct 2019 21:23:11 GMT

I think this is is more accurate:

data _null_;
   input x;
   adj = intnx('year',x,-60,'s');
   datetime = dhms(adj,0,0,0);
   put X= ADJ= DATETIME=datetime22.;
   cards;
42107.66744212963
42109.458969907406
run;

x=42107.667442 adj=20192.667442 datetime=14APR2015:16:01:07
x=42109.45897 adj=20194.45897 datetime=16APR2015:11:00:55

Re: have a question regarding date time in sas

ballardw — Tue, 01 Oct 2019 22:09:56 GMT

@meetagupta wrote:

Customer ID
Time stamp Product Name

147 4/13/15 16:01 Cricket Bat MRF

151 4/15/15 11:00 Badminton Racket Yonex

This is an example of the excel file and I wrote the code to import it into SAS.

FILENAME REFFILE '/home/u42075552/sasuser.v94/Transactional_data.xlsx';

PROC IMPORT DATAFILE=REFFILE
DBMS=XLSX
OUT=WORK.IMPORT2;
GETNAMES=YES;
SHEET="Input";
RUN;

PROC SQL;
CREATE TABLE task1.query AS
SELECT customer_id, time_stamp , product_name
FROM WORK.IMPORT2;
RUN;
QUIT;

Since you have had one issue with XLSX and proc import I might suggest the following steps:

1) change the appearance in Excel of the column to include a 4 digit year and as many seconds or fractions thereof that you may want to actually use. Hint: if you don't need fractions of a second don't include them. You may need to play with the cell formats a bit.

2) Use the File Save As to create a CSV version of the file.

3) Then use Proc Import to read that CSV, changing the dbms to CSV

4) add a GUESSINGROWS=MAX; statement to the Proc import . This will examine more rows of the data before assigning things like variable type and length.

This may have a better chance of getting the import to correctly import a datetime value into a native SAS datetime value and assign an appropriate datetime format.

Re: have a question regarding date time in sas

ChrisNZ — Wed, 02 Oct 2019 01:31:35 GMT

@PaigeMiller You are displaying a date as a datetime and end up with 01JAN1960.

Re: have a question regarding date time in sas

PaigeMiller — Wed, 02 Oct 2019 10:45:56 GMT

@ChrisNZ wrote:

@PaigeMiller You are displaying a date as a datetime and end up with 01JAN1960.

Yes because the original problem statement said "I have a SAS data set and the time&date column"

Re: have a question regarding date time in sas

data_null__ — Wed, 02 Oct 2019 11:03:42 GMT

@ChrisNZ wrote:

I think this is is more accurate:
data _null_;
   input x;
   adj = intnx('year',x,-60,'s');
   datetime = dhms(adj,0,0,0);
   put X= ADJ= DATETIME=datetime22.;
   cards;
42107.66744212963
42109.458969907406
run;
x=42107.667442 adj=20192.667442 datetime=14APR2015:16:01:07
x=42109.45897 adj=20194.45897 datetime=16APR2015:11:00:55

@meetagupta states in their follow-up post that the dates in EXCEL are.

Customer ID	Time stamp	Product Name
147	4/13/15 16:01	Cricket Bat MRF
151	4/15/15 11:00	Badminton Racket Yonex

Re: have a question regarding date time in sas

data_null__ — Wed, 02 Oct 2019 11:07:53 GMT

@ChrisNZ wrote:

I think this is is more accurate:
data _null_;
   input x;
   adj = intnx('year',x,-60,'s');
   datetime = dhms(adj,0,0,0);
   put X= ADJ= DATETIME=datetime22.;
   cards;
42107.66744212963
42109.458969907406
run;
x=42107.667442 adj=20192.667442 datetime=14APR2015:16:01:07
x=42109.45897 adj=20194.45897 datetime=16APR2015:11:00:55

@meetagupta states in a follow-up post that the dates in EXCEL are

Customer ID	Time stamp	Product Name
147	4/13/15 16:01	Cricket Bat MRF
151	4/15/15 11:00	Badminton Racket Yonex

I think the issue is due to EXCEL thinking that 1900 was a leap year.

Re: have a question regarding date time in sas

Tom — Wed, 02 Oct 2019 13:45:32 GMT

The 1900 leap year issue is included in data_null_'s solution by the use of the 30th of December in the calculation to change the date values instead of the 31st of December. If the dates are actually before March 1, 1900 then you might need remove add back that extra day.

Re: have a question regarding date time in sas

ChrisNZ — Wed, 02 Oct 2019 23:29:58 GMT

> I think the issue is due to EXCEL thinking that 1900 was a leap year.

Oh wow, Fair enough, I didn't know of this bug.