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.
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.
You say that your problem is to convert character date values, but in the given data, you have defined your date variables as numeric?
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;
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
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;
@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.
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.
@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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.