topic Re: How to convert char date to date in SQL SAS in SAS Programming

How to convert char date to date in SQL SAS

tjain90 — Fri, 05 May 2017 10:32:27 GMT

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.

Re: How to convert char date to date in SQL SAS

PeterClemmensen — Fri, 05 May 2017 10:39:02 GMT

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

Re: How to convert char date to date in SQL SAS

RW9 — Fri, 05 May 2017 10:41:40 GMT

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;

Re: How to convert char date to date in SQL SAS

tjain90 — Fri, 05 May 2017 10:45:00 GMT

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

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

Re: How to convert char date to date in SQL SAS

tjain90 — Fri, 05 May 2017 10:48:29 GMT

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

Re: How to convert char date to date in SQL SAS

tjain90 — Fri, 05 May 2017 10:56:40 GMT

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;

Re: How to convert char date to date in SQL SAS

RW9 — Fri, 05 May 2017 11:00:50 GMT

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.

Re: How to convert char date to date in SQL SAS

Kurt_Bremser — Fri, 05 May 2017 11:00:03 GMT

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

Re: How to convert char date to date in SQL SAS

Kurt_Bremser — Fri, 05 May 2017 11:02:37 GMT

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

Re: How to convert char date to date in SQL SAS

rawindar — Fri, 05 May 2017 11:38:50 GMT

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;