topic Re: Reading imported date value in SAS and using SQL to query that USing SAS EG7.1 in SAS Programming

Reading imported date value in SAS and using SQL to query that USing SAS EG7.1

ashutosh83 — Wed, 21 Nov 2018 14:12:59 GMT

Hi all,

I have some date in excel it have column date1 as 1/31/2011 4:42:40 AM

when imported in SAS with format date9. it's shows as 31Jan2011

when I see the unformated value it's like 18658.196296. when I query the table it does not return any record.

data new;

format date1 date9.;

input date1;

datalines;

18658.196296

;

run;

/* But when I use below query the table it did not return any record*/

proc sql;

select * from new

where date1="31jan2011"D;

quit;

In Excel:

Data1
1/31/2011 4:42:40 AM

data new;
format date1 date9.;
input date1;
datalines;
18658.196296
;
run;

Gives:
date1
31Jan2011

But when I use below query the table it did not return any record

proc sql;
select * from new
where date1="31jan2011"D; /*datepart is also not working*/
quit;

Why this happening and how we can handle this.

Re: Reading imported date value in SAS and using SQL to query that USing SAS EG7.1

RW9 — Wed, 21 Nov 2018 14:24:10 GMT

At no point in your code, other than "I have some date in excel it have column date1 as 1/31/2011 4:42:40 AM", do you mention a datetime, only dates. Post example test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

There is no point in me trying to guess what you have from the post. The steps you provide create date data. If you want datetime then fix your import process!

Re: Reading imported date value in SAS and using SQL to query that USing SAS EG7.1

Peter_C — Wed, 21 Nov 2018 14:24:46 GMT

It holds a fractional value
Filter it
>= '31jan2011'd and < '1feb2011'd

Re: Reading imported date value in SAS and using SQL to query that USing SAS EG7.1

Kurt_Bremser — Wed, 21 Nov 2018 14:27:21 GMT

'31jan2011'd

is a date value and therefore an integer (!!) count of days from 1960-01-01.

18658.196296

is NOT an integer value and therefore can't be equal to the above date value. Use the floor() function first.

Re: Reading imported date value in SAS and using SQL to query that USing SAS EG7.1

Tom — Wed, 21 Nov 2018 14:32:45 GMT

Excel stores datetime values in days with the timepart as a fraction of a day.

SAS stores datetime values as seconds.

111  data check;
112    x=18658.196296 ;
113    put x date9.;
114    dt = x*'24:00't ;
115    put dt datetime19.;
116  run;

31JAN2011
 31JAN2011:04:42:40

Re: Reading imported date value in SAS and using SQL to query that USing SAS EG7.1

SuryaKiran — Wed, 21 Nov 2018 15:06:37 GMT

I suspect that your are not giving the right informat while reading the date values from Excel. You will see those decimal values if you try to read the datetime values in excel as numeric values (i.e. Informat date1 12.5 ). You may need to change the informat values to datetime26. Read datetime values as datetime.

When you have them as datetime in SAS then you have to extract date part from datetime

where datepart(date1)="31jan2011"D

Re: Reading imported date value in SAS and using SQL to query that USing SAS EG7.1

data_null__ — Wed, 21 Nov 2018 15:56:31 GMT

A mildly interesting side note. The DHMS function recognizes the decimal portion and processes it accordingly.

8    data _null_;
9       x=18658.196296;
10      put x date9.;
11      dt1 = x*'24:00't;
12      dt2 = dhms(x,0,0,0);
13      put (dt:) (/=datetime19.);
14      run;

31JAN2011

dt1=31JAN2011:04:42:40
dt2=31JAN2011:04:42:40

Re: Reading imported date value in SAS and using SQL to query that USing SAS EG7.1

Peter_C — Wed, 21 Nov 2018 16:09:15 GMT

Might be interesting to demonstrate IF there are decimal fractions of a second in the value DT1=
Put dt1= datetime21.2 ;

Re: Reading imported date value in SAS and using SQL to query that USing SAS EG7.1

ashutosh83 — Thu, 22 Nov 2018 12:38:56 GMT

Yes while importing the file I changed the source_informat to anydtdtew. and it resolved the issue.

Still not sure how it did .

data a;
input date1 anydtdte.;
datalines;
1/31/2011 4:42:40 AM
;
run;

proc sql;
select date1 as date2 format date9. from a
where date1 eq "31jan2011"D;
quit;