asked on

reject rows based on condition

hi,
I have do sumthing like this in datavalidation transformer
i have mod_date =2011-04-19 and mod_scan_time 15:38:21
so we need to combine them 2011-04-1915:38:21 and write a condition
if mod_combine_timestamp > currenttimestamp reject it else pass it

so basic idea should be compare mod_date+mod_scan_time against some kind of currenttimestamp
inbuild macro so use custom validation table under data validation
and can write a condition if mod_date+mod_scan_time > currenttimestamp then reject else not
I need help in writing a condition
i did tried this in expression but not working
mod_date|':'|mod_scan_time > &SYSTIME

ASKER CERTIFIED SOLUTION

d507201

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

d507201

I don't work with date-time values very often, so there is likely a better solution.

sam2929

ASKER

so we can't contact and then compare with currenttimestamp is there a easy sql i can test
and then apply logic
as mod_date is yymmdd8
mod_time is time8

d507201

I don't know what you mean by 'so we can't contact...'.

Your mod_date and mod_time formats indicate that they are SAS date and time variables so the DHMS function will work just fine.

I don't do a lot of SAS SQL but something like this should work. The variable currenttimestamp must be a SAS date-time variable, such as '10NOV2010:03:49:19'dt

proc sql;
select *
, dhms(mod_date,hour(mod_time),minute(mod_time ,second(tmod_time) as dataTime
from inputDataSet
where calculated dataTime > currenttimestamp ;
quit;

sam2929

ASKER

ok so what you are suggesting is in expression editor do
dhms(mod_date,hour(mod_time),minute(mod_time ,second(tmod_time) >currenttimestamp
right

Aloysius Low

is it a requirement that you have to compare against a date time? i'm thinking of using 2 separate comparison - that mod_date >= date() and mod_time > time() then reject the record

or is this considered and deemed invalid/not workable?

sam2929

ASKER

i think comparison for mod_date and mod_time should be ok too as idea ia same
but now i am thinking what if down the line they say lets do sumthing like
mod_date+mod_time >cureenttimestamp + 2 days

Aloysius Low

when that happens you could do like (mod_date >= (date() + 2) and (mod_time > time()) - how does that sound?

sam2929

ASKER

so date() and time() will be system dates right

sam2929

ASKER

looks like it don't like date()
if(mod_date<date() and mod_time<time()) then
30235 do;
30236 x_column = "";
30237 x_type = "future_date: True";
30238 x_action = "Move row to error table";
30239 output work.etls_Exceptions;
30240
30241 i_excp = 1;
30242 i_move = 1;

theartfuldazzler

Hi

Just be careful in the logic. Looking at the date and time separately will cause incorrect results.

Rather use:

if (mod_date > today() or (mod_date = today() and mod_time > time() )
then do;
x_type = "Future date: true";
end;

theartfuldazzler

arghh... missing bracket:

if (mod_date > today() or (mod_date = today() and mod_time > time() ))
then do;
x_type = "Future date: true";
end;