BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tjain90
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

9 REPLIES 9
PeterClemmensen
Tourmaline | Level 20

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
Fluorite | Level 6
Hi draycut, it was an typo. Please assume it as date1$10.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Super User

@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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

@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
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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