Todays date in Proc sql

nickb — Tue, 12 Apr 2011 13:49:08 GMT

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

Re: Todays date in Proc sql

DBailey — Tue, 12 Apr 2011 14:30:55 GMT

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.

topic Todays date in Proc sql in SAS Procedures

Todays date in Proc sql

Re: Todays date in Proc sql