Update Teradata table

[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 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

needed output:one

===========

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

[ChrisNZ]

1. Use the {i} icon or the running man to post log or code.

   This avoids issues like smileys.

2. Are you updating Teradata or a WORK data set?

3. What variable do you increment and why and when?
  There is no where condition on your SET statement

4. Since you seem to be driving the process from a data step,
   call execute() might be better suited than macros.

5. You can use the exist() function to test that a table exists and avoid error messages

6. Changing the thread title to something more relevant than "sas eg"

7. Loaded not loeaded 🙂