• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

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
0
sam2929
Asked:
sam2929
  • 5
  • 3
  • 2
  • +1
1 Solution
 
d507201Database Marketing ConsultantCommented:
Are mod_date and mod_scan_time SAS date and SAS time variables?  If so you have to use the DHMS function to combine them and return a numeric value that represents a SAS datetime value.

DHMS(date,hour,minute,second)

     dataTime= dhms(mod_date,hour(mod_scan_time),minute(mod_scan_time ,second(tmod_scan_time ;

You can apply the datetime format to make the variable more readable if you print it.

     format datetime dateTime16.

Then compare the derived dateTime variable to your criteria.  This presumes that the variable currentTimeStamp is also a SAS dateTime value.

     if dataTime > currentTimeStamp then delete;
0
 
d507201Database Marketing ConsultantCommented:
I don't work with date-time values very often, so there is likely a better solution.
0
 
sam2929Author Commented:
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
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
d507201Database Marketing ConsultantCommented:
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;
0
 
sam2929Author Commented:
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


0
 
Aloysius LowCommented:
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?
0
 
sam2929Author Commented:
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
0
 
Aloysius LowCommented:
when that happens you could do like (mod_date >= (date() + 2)  and (mod_time > time()) - how does that sound?
0
 
sam2929Author Commented:
so date() and time() will be system dates right
0
 
sam2929Author Commented:
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;
0
 
theartfuldazzlerCommented:
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;
0
 
theartfuldazzlerCommented:
arghh... missing bracket:


 if (mod_date > today() or (mod_date = today() and mod_time > time() ))
   then do;
     x_type = "Future date: true";
end;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now