topic Re: deleting record based on text field and date selection in SAS Programming

deleting record based on text field and date selection

tparvaiz — Fri, 19 May 2017 20:12:40 GMT

I need to delete records from table temp_weekly_data where the date is less than today and status is equal to A

following code is deleting records where the date is less than today(), ignoring if the staus value

data temp_weekly_data;
set DB.TABLE(where = (&date1 <= date_my < &date2 ) );

if status in ("A") and datepart(date_my) < today() then delete;

run;

staus field is of type Text, 10 character length

Thanks in advance

Re: deleting record based on text field and date selection

Shmuel — Fri, 19 May 2017 20:28:59 GMT

Your code should work fine only in case status is always in capital letter.

Are &date1 and &date2 given as datetime or date only ?

Are your where consistent with the IF statement ? (either date variable or datetime variable)

You may test:

data temp_weekly_data;
   set DB.TABLE(where = (&date1 <= date_my < &date2 ) );
       if upcase(status) = "A"  
         and datepart(date_my) < today() then delete;
run;

Re: deleting record based on text field and date selection

art297 — Fri, 19 May 2017 21:53:37 GMT

You mentioned that status is a 10 character length field. Are you possibly wanting to delete a record if its status field contains an "A"? If so, you could use:

data db.table;
  informat status $10.;
  length status $10;
  input status;
  cards;
A
BC
 A
ABC
BCD
;

data temp_weekly_data;
  set DB.TABLE (where = (&date1 <= date_my < &date2 ) );
  if find(status,"A") and datepart(date_my) < today() then delete;
run;

Art, CEO, AnalystFinder.com