CODEWITHSUNDEEP
datesasproc-sql
shecode
shecode

Reputation: 1726

SAS how to change a date format in a where clause

I have some data which has the date stored in the yymmn6. format and others that are stored in the date9. format

I want to do a proc sql statement using the where clause but in order to compare two dates the date9. fields need to be in the yymmd6. format

is there an equivalent of this which can be used in the where clause

proc sql;
  select dt format=yymmn6., 
  from have;
quit;

i.e. 

proc sql;
  select *
  from have WHERE dt format=yymmn6. = other_date;
quit;

Upvotes: 1

Views: 3970

Answers (1)

Chris J
Chris J

Reputation: 7769

A SAS variable formatted as a date is simply a number (representing days since 01JAN1960).

If other_date is also a SAS date value (regardless of format), you don't need to apply a format to either variable.

If other_date is a string representing a date (e.g. 201501), you can apply the format to dt, or input other_date to a SAS date.

/* Apply format to dt to match other_date */
proc sql ;
  select * 
  from have
  where put(dt,yymmn6.) = other_date ;
quit ;

/* Be careful here though as 201501 will input as 2015-01-01 */
proc sql ;
  select *
  from have
  where dt = input(other_date,yymm6.) ;
quit ;

Upvotes: 3

Related Questions