?
Solved

reject rows based on condition

Posted on 2011-05-05
12
Medium Priority
?
654 Views
Last Modified: 2013-11-16
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
Comment
Question by:sam2929
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 7

Accepted Solution

by:
d507201 earned 2000 total points
ID: 35701550
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
 
LVL 7

Expert Comment

by:d507201
ID: 35701559
I don't work with date-time values very often, so there is likely a better solution.
0
 

Author Comment

by:sam2929
ID: 35701591
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 7

Expert Comment

by:d507201
ID: 35701751
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
 

Author Comment

by:sam2929
ID: 35702144
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
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 35703439
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
 

Author Comment

by:sam2929
ID: 35704104
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
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 35704279
when that happens you could do like (mod_date >= (date() + 2)  and (mod_time > time()) - how does that sound?
0
 

Author Comment

by:sam2929
ID: 35704315
so date() and time() will be system dates right
0
 

Author Comment

by:sam2929
ID: 35705696
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
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 35705930
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
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 35705940
arghh... missing bracket:


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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question