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

[ashutosh83]

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.

[RW9]

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

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! 

[Peter_C]

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

[Kurt_Bremser]

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

[Tom]

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

[data_null__]

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

[SuryaKiran · Accepted solution]

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

[ashutosh83]

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;

[Peter_C]

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