DATA Step, Macro, Functions and more

deleting record based on text field and date selection

Reply
Frequent Contributor
Posts: 129

deleting record based on text field and date selection

Hi

 

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

Trusted Advisor
Posts: 1,392

Re: deleting record based on text field and date selection

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;
PROC Star
Posts: 7,363

Re: deleting record based on text field and date selection

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

 

Ask a Question
Discussion stats
  • 2 replies
  • 109 views
  • 0 likes
  • 3 in conversation