need to delete the rows which are older than 10 days and set id=1 with auto increment value.

[Hema_12]

Hi All,

query;- i need to delete the rows which are older than 10 days and set id=1 with auto increment value.

database is teradata;

please suggest  possible solutions.

Thanks in advance.

🙂

i have written this piece of code

------------------------------------------

options mprint mlogic symbolgen;
%macro clean;
data one;
set work.process_cleanup;
today= today();
/*max_date=max(start_datetime);*/
max_date_1=datepart(start_datetime);
/*seven_date=intnx('day',today,-7);*/
format today date10. max_date_1 date10. /*seven_date date10.*/;

/*%if max_date_1<today %then %do;*/
/*output out_t;*/
proc sql;
delete from work.one where max_date_1< (today()-10);
/*select id from work.one where id=max(id);*/
update work.one
set id=(select max(id)-(max(id)-1) from work.process_cleanup);
quit;
/*%end;*/
/*%else;*/
/*output two;*/
run;
%mend clean;
%clean;

data setrocess_cleanup

========

ID	PROCESS_NAME	START_DATETIME	END_DATETIME	STATUS	MESSAGE
137	process_1	29/04/2019 07:54	29/04/2019 07:54	F	Loeaded successfully
136	process_2	29/04/2019 07:15	29/04/2019 07:15	F	Loeaded successfully
135	process_3	28/04/2019 07:15	28/04/2019 07:15	E	SASERROR: 8888  File does not exist. Please check process log in
134	process_4	27/04/2019 07:15	27/04/2019 07:15	F	Loeaded successfully
133	process_5	26/04/2019 09:24	26/04/2019 09:24	F	Loeaded successfully
132	process_6	26/04/2019 09:18	26/04/2019 09:18	E	SASERROR: 8888  File does not exist. Please check process log in
131	process_7	26/04/2019 09:09	26/04/2019 09:10	F	Loeaded successfully
130	process_8	26/04/2019 08:59	26/04/2019 09:01	F	Loeaded successfully
129	process_9	26/04/2019 08:53	26/04/2019 08:54	F	Loeaded successfully
128	process_10	26/04/2019 08:30	26/04/2019 08:30	F	Loeaded successfully
127	process_11	21/04/2019 07:15	21/04/2019 07:15	F	Loeaded successfully
126	process_12	20/04/2019 07:15	20/04/2019 07:15	F	Loeaded successfully
125	process_13	19/04/2019 07:15	19/04/2019 07:15	F	Loeaded successfully
124	process_14	18/04/2019 07:15	18/04/2019 07:15	F	Loeaded successfully
123	process_15	17/04/2019 07:15	17/04/2019 07:15	F	Loeaded successfully
122	process_16	16/04/2019 07:15	16/04/2019 07:15	F	Loeaded successfully
121	process_17	15/04/2019 07:15	15/04/2019 07:16	F	Loeaded successfully
120	process_18	14/04/2019 07:15	14/04/2019 07:15	F	Loeaded successfully
119	process_19	13/04/2019 07:15	13/04/2019 07:15	F	Loeaded successfully
118	process_20	12/04/2019 07:15	12/04/2019 07:15	F	Loeaded successfully
117	process_21	11/04/2019 07:15	11/04/2019 07:15	F	Loeaded successfully
116	process_22	10/04/2019 07:15	10/04/2019 07:15	F	Loeaded successfully
115	process_23	09/04/2019 07:15	09/04/2019 07:15	F	Loeaded successfully
114	process_24	08/04/2019 07:17	08/04/2019 07:21	F	Loeaded successfully
113	process_25	07/04/2019 07:15	07/04/2019 07:15	F	Loeaded successfully
112	process_26	06/04/2019 07:15	06/04/2019 07:15	F	Loeaded successfully
111	process_27	05/04/2019 18:00	05/04/2019 18:07	F	Loeaded successfully
110	process_28	05/04/2019 17:31	05/04/2019 17:38	F	Loeaded successfully
109	process_29	05/04/2019 16:33	05/04/2019 17:31	F	Loeaded successfully
108	process_30	05/04/2019 15:56	05/04/2019 16:33	F	Loeaded successfully
107	process_31	05/04/2019 15:15	05/04/2019 15:56	F	Loeaded successfully
106	process_32	05/04/2019 14:04	05/04/2019 15:15	F	Loeaded successfully

needed outputne

===========

ID	PROCESS_NAME	START_DATETIME	END_DATETIME	STATUS	MESSAGE
1	process_1	29/04/2019 07:54	29/04/2019 07:54	F	Loeaded successfully
2	process_2	29/04/2019 07:15	29/04/2019 07:15	F	Loeaded successfully
3	process_3	28/04/2019 07:15	28/04/2019 07:15	E	SASERROR: 8888 File does not exist. Please check process log in
4	process_4	27/04/2019 07:15	27/04/2019 07:15	F	Loeaded successfully
5	process_5	26/04/2019 09:24	26/04/2019 09:24	F	Loeaded successfully
6	process_6	26/04/2019 09:18	26/04/2019 09:18	E	SASERROR: 8888  File does not exist. Please check process log
7	process_7	26/04/2019 09:09	26/04/2019 09:10	F	Loeaded successfully
8	process_8	26/04/2019 08:59	26/04/2019 09:01	F	Loeaded successfully
9	process_9	26/04/2019 08:53	26/04/2019 08:54	F	Loeaded successfully
10	process_10	26/04/2019 08:30	26/04/2019 08:30	F	Loeaded successfully
11	process_11	21/04/2019 07:15	21/04/2019 07:15	F	Loeaded successfully

[PaigeMiller]

As I look at your code, I see no real benefit to doing this in a macro, and I also don't see any benefit to doing this with a DATA step followed by PROC SQL.

Something like this might work

data one;
    set work.process_cleanup;
    if datepart(start_datetime)<(today()-10) then delete;
    id+1;
run;
    

[Hema_12]

Hi Paige,

Thank you so much for your solution.But I  already achieved the deletion of rows.

it deleted the rows which are older than 10 days but not setting the id again from 1. 

i need to alter the id from 1 after deletion the rows.

Thanks & Regards,

Hema 

[andreas_lds]

To bad that you haven't posted data in usable form, so the following code is untested:

data one;
    set work.process_cleanup;
    retain new_id 1;
	
	id = new_id;
	
    if datepart(start_datetime)<(today()-10) then delete;
	
	new_id = new_id + 1;
run;

[Hema_12]

Hi ,

Already posted data in my main post.

i have checked your code but it not able to auto increment the id column again.

please check the needed output data here it is.

input data set --->process_cleanup

========

ID	PROCESS_NAME	START_DATETIME	END_DATETIME	STATUS	MESSAGE
137	process_1	29/04/2019 07:54	29/04/2019 07:54	F	Loeaded successfully
136	process_2	29/04/2019 07:15	29/04/2019 07:15	F	Loeaded successfully
135	process_3	28/04/2019 07:15	28/04/2019 07:15	E	SASERROR: 8888  File does not exist. Please check process log in
134	process_4	27/04/2019 07:15	27/04/2019 07:15	F	Loeaded successfully
133	process_5	26/04/2019 09:24	26/04/2019 09:24	F	Loeaded successfully
132	process_6	26/04/2019 09:18	26/04/2019 09:18	E	SASERROR: 8888  File does not exist. Please check process log in
131	process_7	26/04/2019 09:09	26/04/2019 09:10	F	Loeaded successfully
130	process_8	26/04/2019 08:59	26/04/2019 09:01	F	Loeaded successfully
129	process_9	26/04/2019 08:53	26/04/2019 08:54	F	Loeaded successfully
128	process_10	26/04/2019 08:30	26/04/2019 08:30	F	Loeaded successfully
127	process_11	21/04/2019 07:15	21/04/2019 07:15	F	Loeaded successfully
126	process_12	20/04/2019 07:15	20/04/2019 07:15	F	Loeaded successfully
125	process_13	19/04/2019 07:15	19/04/2019 07:15	F	Loeaded successfully
124	process_14	18/04/2019 07:15	18/04/2019 07:15	F	Loeaded successfully
123	process_15	17/04/2019 07:15	17/04/2019 07:15	F	Loeaded successfully
122	process_16	16/04/2019 07:15	16/04/2019 07:15	F	Loeaded successfully
121	process_17	15/04/2019 07:15	15/04/2019 07:16	F	Loeaded successfully
120	process_18	14/04/2019 07:15	14/04/2019 07:15	F	Loeaded successfully
119	process_19	13/04/2019 07:15	13/04/2019 07:15	F	Loeaded successfully
118	process_20	12/04/2019 07:15	12/04/2019 07:15	F	Loeaded successfully
117	process_21	11/04/2019 07:15	11/04/2019 07:15	F	Loeaded successfully
116	process_22	10/04/2019 07:15	10/04/2019 07:15	F	Loeaded successfully
115	process_23	09/04/2019 07:15	09/04/2019 07:15	F	Loeaded successfully
114	process_24	08/04/2019 07:17	08/04/2019 07:21	F	Loeaded successfully
113	process_25	07/04/2019 07:15	07/04/2019 07:15	F	Loeaded successfully
112	process_26	06/04/2019 07:15	06/04/2019 07:15	F	Loeaded successfully
111	process_27	05/04/2019 18:00	05/04/2019 18:07	F	Loeaded successfully
110	process_28	05/04/2019 17:31	05/04/2019 17:38	F	Loeaded successfully
109	process_29	05/04/2019 16:33	05/04/2019 17:31	F	Loeaded successfully
108	process_30	05/04/2019 15:56	05/04/2019 16:33	F	Loeaded successfully
107	process_31	05/04/2019 15:15	05/04/2019 15:56	F	Loeaded successfully
106	process_32	05/04/2019 14:04	05/04/2019 15:15	F	Loeaded successfully

output dataset

===========

ID	PROCESS_NAME	START_DATETIME	END_DATETIME	STATUS	MESSAGE
1	process_1	29/04/2019 07:54	29/04/2019 07:54	F	Loeaded successfully
2	process_2	29/04/2019 07:15	29/04/2019 07:15	F	Loeaded successfully
3	process_3	28/04/2019 07:15	28/04/2019 07:15	E	SASERROR: 8888 File does not exist. Please check process log in
4	process_4	27/04/2019 07:15	27/04/2019 07:15	F	Loeaded successfully
5	process_5	26/04/2019 09:24	26/04/2019 09:24	F	Loeaded successfully
6	process_6	26/04/2019 09:18	26/04/2019 09:18	E	SASERROR: 8888  File does not exist. Please check process log
7	process_7	26/04/2019 09:09	26/04/2019 09:10	F	Loeaded successfully
8	process_8	26/04/2019 08:59	26/04/2019 09:01	F	Loeaded successfully
9	process_9	26/04/2019 08:53	26/04/2019 08:54	F	Loeaded successfully
10	process_10	26/04/2019 08:30	26/04/2019 08:30	F	Loeaded successfully
11	process_11	21/04/2019 07:15	21/04/2019 07:15	F	Loeaded successfully

[andreas_lds]

You have posted tables, nice to view but useless, because they don't show any information about column types and lengths. See How to convert datasets to data steps to learn how to post data in usable form.

[PaigeMiller]

---

@Hema_12 wrote:

Hi Paige,

Thank you so much for your solution.But I  already achieved the deletion of rows.

it deleted the rows which are older than 10 days but not setting the id again from 1. 

i need to alter the id from 1 after deletion the rows.

---

It's not clear to me what this means. The code I provided gives ID number from 1 to 11 in the output data set, just as your example shows. If that's not what it does, then please explain further, and show us the code you used, and show us the results that are not what you want.