topic Re: Create a new variable and give its value as updated when column is updated in SAS Programming

Create a new variable and give its value as updated when column is updated

nid197 — Tue, 19 Feb 2019 14:31:09 GMT

I have this sample code-i want to create a column in teradata table when data is updated.please help
I have triend using status='updated' in execute block but i am not able to update the table.

Proc sql,
Connect to tera,
%do i=1 to &cnt;
Execute(update &table
Set &colm=bquote('&&new&i)
&colm2=bquote('&&new2&i);
%end;
Quit;

Re: Create a new variable and give its value as updated when column is updated

RW9 — Tue, 19 Feb 2019 14:58:54 GMT

You should do this on your database using triggers:

https://community.teradata.com/t5/Database/After-Insert-trigger/td-p/27510

Do not try to run one piece of software through another, use the software as given, its far far simpler and easier to manage.

The database connections are there to download and upload data to and from SAS/database, its not there for doing database tasks.

Re: Create a new variable and give its value as updated when column is updated

nid197 — Tue, 19 Feb 2019 15:05:30 GMT

But i am using various conditions to update a variable and i just want to check if the column i am targetting is being updated or not..for this i have to create a new column as a status of yes or no.can i do that?is it possible.

Re: Create a new variable and give its value as updated when column is updated

RW9 — Tue, 19 Feb 2019 15:15:04 GMT

No, what you are doing is using Macro, to generate a whole series of Base SAS code, which is converted to SQL, and then passed over to the Teradata database to execute. As you can see that is four different peices of software. To confound it further, your next question is how to check if the database - last one on the series of technologies - has completed the task. To do that you would then need to read the data back off the database to see if it is correct. Therefore, not just 4 technologies, but at least one write to the database, and one read from the database.

As for "for this i have to create a new column as a status of yes or no.can i do that" - that very much depends, you are altering the table structure on the database, are you allowed/able to do such a thing? I would have thought the database structure would be fixed?

Anyways, you would submit a alter table sql statement, something like:

proc sql;
...
execute('alter table... add status char(20);');
...
quit;

Then you could set that as part of your update statement.