Help using Base SAS procedures

Todays date in Proc sql

Reply
Contributor
Posts: 65

Todays date in Proc sql

I'm working on a proc sql statement and I only want todays date to return and it's not working right. Below is the code:

LIBNAME LEAVE OLEDB PROPERTIES=( "Initial Catalog"=Workflow) DATASOURCE="Argent\db1"
PROMPT=NO PROVIDER=SQLOLEDB.1 USER=sasadmin PASSWORD="{sasenc}B089914C1F024AB03FF78DBE40F4421B" ;

%let today = %sysfunc(today(),YYMMDD10.);


FILENAME D_FILE "d:\temp\nick_tmp\leavedetail%sysfunc(date(),mmddyy6.).txt";
FILENAME H_FILE "d:\temp\nick_tmp\leavedetail.txt";

proc sql;

create table WORK.LeaveData as
SELECT reverse(substr(reverse('0000000' || trim(lr.TartanID)),1,7)) as TartanID,
datepart(lr.TDate) as TDate format=YYMMDD10.,
reverse(substr(reverse('0000000' || trim(lr.submittedTo)),1,7)) as SubmittedTo,
lr.Classification,
lr.IsFMLA,
lr.IsRevised,
datepart(ld.SDate) as SDate format=YYMMDD10.,
datepart(ld.RDate) as RDate format=YYMMDD10.,
datepart(wfi.exitDate) as ExitDate format=YYMMDD10.,
ld.Hours format=10.2,
ld.Comment,
ld.LeaveType
FROM LEAVE.leaveRequest as lr
INNER JOIN LEAVE.leaveRequestDetail as ld
ON ld.formid = lr.id
INNER JOIN LEAVE.workflowinfo as wfi
ON wfi.forminfoid = lr.id
AND wfi.statusid = '3'

WHERE wfi.exitdate = &today <<<<<<<<<<<<<<<<<<<<<<<
Super Contributor
Posts: 578

Re: Todays date in Proc sql

I think the variable in the proc sql is being parsed as


WHERE wfi.exitdate = 2011-04-12

which is not a valid date. You could use

WHERE wfi.exitdate = "&today"d

or

WHERE wfi.exitdate = date()

or

WHERE datepart(wfi.exitdate) = date()

if it is a datetime variable.
Ask a Question
Discussion stats
  • 1 reply
  • 8454 views
  • 0 likes
  • 2 in conversation