DATA Step, Macro, Functions and more

How to convert char date to date in SQL SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

How to convert char date to date in SQL SAS

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.


Accepted Solutions
Solution
‎05-05-2017 08:14 AM
Super User
Super User
Posts: 7,977

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

[ Edited ]

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.

View solution in original post


All Replies
PROC Star
Posts: 763

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

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

Occasional Contributor
Posts: 15

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

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

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Super User
Super User
Posts: 7,977

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

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;
Occasional Contributor
Posts: 15

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

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

Occasional Contributor
Posts: 15

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

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;
Super User
Posts: 7,832

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


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎05-05-2017 08:14 AM
Super User
Super User
Posts: 7,977

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

[ Edited ]

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.

Super User
Posts: 7,832

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


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 70

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

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 309 views
  • 1 like
  • 5 in conversation