How to convert char date to date in SQL SAS

[tjain90]

Hi Everyone,

Can you please let me know that how I can convert Character Date value and use in where claus of proc sql.

For example :

I have data like:

data ab;
input date1;
cards;
05/21/1963
03/20/1967
07/23/1961
08/03/1975
01/24/1960
;
run;

Above is only an example case. So Now i want to fetch all those rows having date greater than 2 Feb 1963. 

So right now i am unable to first convert this date1 to date using proc sql then, i beleive after that we can use gt in where clause of sql syntax.

[PeterClemmensen]

You say that your problem is to convert character date values, but in the given data, you have defined your date variables as numeric?

[tjain90]

Hi draycut, it was an typo. Please assume it as date1$10.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[RW9]

Its the same as reading or using dates in any other part of SAS:

data ab;
  informat date1 mmddyy10.;
  input date1;
  format date1 date9.;
cards;
05/21/1963
03/20/1967
07/23/1961
08/03/1975
01/24/1960
;
run;
data have;
  chk='01jan1990'd;
run;

proc sql;
  create table WANT as
  select *
  from   AB A
  left join HAVE B
  on    1=1
  where  B.CHK > A.DATE1;
quit;

[tjain90]

Thanks RW9.

But the scenerio is i cannot change this original data set. So i am bounded to use proc sql. So is there any way to convert this using sql only. I accept it would not good approach but scenerio is forcing to use proc sql

[tjain90]

Also RW9, Can you please check what wrong I am doing in this code ?

Why below error occurs:

ERROR: Invalid date/time/datetime constant '03/20/1967'd.

data ab;
input date mmddyy10.;
cards;
05/21/1963
03/20/1967
07/23/1961
08/03/1975
01/24/1960
;
run;

proc sql;
select date format mmddyy10. from ab where date<'03/20/1967'd;
quit;

[Kurt_Bremser]

---

@tjain90 wrote:

Also RW9, Can you please check what wrong I am doing in this code ?

Why below error occurs:

ERROR: Invalid date/time/datetime constant '03/20/1967'd.

data ab;
input date mmddyy10.;
cards;
05/21/1963
03/20/1967
07/23/1961
08/03/1975
01/24/1960
;
run;

proc sql;
select date format mmddyy10. from ab where date<'03/20/1967'd;
quit;

---

You should start to heed my Maxim #1 and read the documentation. The proper way to write your SAS date literal is '20mar1967'd.

[RW9 · Accepted solution]

Edit, in your given scenario you can only use date9 style dates as '....'d values.  Use input():

proc sql;
select date format mmddyy10. from ab where date < input('03/20/1967',mmddyy10.);
quit;

Doesn't make any sense.  What does not being able to change the dataset have todo with using SQL?  Why can't you make new datasets?  Anyway, as I said, you use dates in SQL exactly the same as in datastep, you use input() or put() to change datatypes:

proc sql;
  ...
  where input(chardate,mmddyy10.) <= otherdatevar
...
quit;

If you don't provide the required information for us to know what you are doing the answer will be general.

[Kurt_Bremser]

---

@tjain90 wrote:

Thanks RW9.

But the scenerio is i cannot change this original data set. So i am bounded to use proc sql. So is there any way to convert this using sql only. I accept it would not good approach but scenerio is forcing to use proc sql

---

That does not prohibit recreating the data set with a DATA step or SQL in WORK. So you are NOT forced to use SQL only.

[rawindar]

data ab;

length date1 $10;

input date1 $;

cards;

05/21/1963

03/20/1967

07/23/1961

08/03/1975

01/24/1960

;

run;

data cd;

format date2 date9.;

set ab;

date2=input(date1,mmddyy10.);

run;

proc sql;

create table test as select date1,input(date1,mmddyy10.) as date2 format=mmddyy8. from

ab;

quit;