have a question regarding date time in sas

[meetagupta]

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.

[data_null__]

---

@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

[data_null__]

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

[ChrisNZ]

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

[data_null__]

---

@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

[data_null__]

---

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

[Tom]

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.

[ChrisNZ]

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

[PaigeMiller]

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;

[ChrisNZ]

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

[PaigeMiller]

---

@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"

[ballardw]

---

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

[meetagupta]

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;

[ballardw]

---

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