topic Re: Update only new values with conditions in SAS Programming

Update only new values with conditions

Aleixo — Thu, 08 Feb 2018 15:48:53 GMT

Hi,

I want to update a table (one column) from WORK.FILTER_FOR_TESTE_NOVA_LISTADISJU but only new values(not same values) with a condition.

With this next code the column is all updated with new value and the others values changed to NULL because the condition.

What i want is change only the new value on the column with these conditions and not all column.

proc sql;
title 'Nova Lista Disjuntores';
create table WORK.Nova_Lista_Disjuntores as
	select * from DIST_DDC.DDC_APA_LISTADISJUNTORESRND;
update WORK.Nova_Lista_Disjuntores as u
	set Último_Disparo=(select Último_Disparo from WORK.FILTER_FOR_TESTE_NOVA_LISTADISJU as n where u.Tag=n.Tag AND u.Último_Disparo < n.Último_Disparo)
	where u.Último_Disparo in (select Último_Disparo from WORK.FILTER_FOR_TESTE_NOVA_LISTADISJU);
	select Último_Disparo from WORK.Nova_Lista_Disjuntores;
run;

Regards,
Aleixo

Re: Update only new values with conditions

ballardw — Thu, 08 Feb 2018 16:10:33 GMT

Can you provide a few records from DIST_DDC.DDC_APA_LISTADISJUNTORESRND and WORK.FILTER_FOR_TESTE_NOVA_LISTADISJU (or something that looks like them if the data is sensitive), what the result should look like (we really can't tell what "new" is) and tell us if there is a primary key or unique record identifier for the DIST_DDC.DDC_APA_LISTADISJUNTORESRND data set?

Re: Update only new values with conditions

RW9 — Thu, 08 Feb 2018 16:20:14 GMT

Why not simplify your code a lot and use datastep:

data nova_lista_disjuntores;
  set nova_lista_disjuntores;
  if <condition> then ...;
  if <condition> then ...;
  if <condition> then ...;
run;

Re: Update only new values with conditions

Aleixo — Thu, 08 Feb 2018 16:20:54 GMT

Thanks for your answer.

I want to change not a column but only a row in that column. The code for that

set Último_Disparo=(select Último_Disparo from WORK.FILTER_FOR_TESTE_NOVA_LISTADISJU as n where u.Tag=n.Tag AND u.Último_Disparo < n.Último_Disparo)

Tag is a unique identifier

Table DDC
          TAG   Último_Disparo
XXXX YYYY ZZZZZ 01SEP2015
XXXX YYYY ZZZZZ 31JAN2015

Table WORK.FILTER......
          TAG   Último_Disparo
XXXX YYYY ZZZZZ 01SEP2015
XXXX YYYY ZZZZZ 31JAN2017

What i want is update the second line in DDC with the new value(new date early) from Work.filter......... and keep in talbe DDC the first line because not changed.

Regards,
Aleixo

Re: Update only new values with conditions

Aleixo — Thu, 08 Feb 2018 16:39:49 GMT

That code could change only a row in a column(nova_lista_disjuntores)?

Re: Update only new values with conditions

Aleixo — Thu, 08 Feb 2018 16:43:22 GMT

The main idea is compare 2 diferent columns and change some rows, not all rows of the column

Re: Update only new values with conditions

RW9 — Fri, 09 Feb 2018 09:01:29 GMT

Follow the guidance on posting a question. Provide some test data in the form of a datastep so that we have something to run on. Secondly show what you expect as output and describe the process between. I have no idea from what you have posted what your trying to do - "The main idea is compare 2 diferent columns and change some rows," - this is conditional logic, which you can do in a datastep just like you would in SQL, remember your programming in SAS not SQL, so good idea to learn the language you are working in before trying to use additional components like SQL.

Re: Update only new values with conditions

Aleixo — Fri, 09 Feb 2018 09:13:48 GMT

Thank you for your advise. i am new with sas an also sql. I will pay attention to SAS programming and not so sql.

Re: Update only new values with conditions

Aleixo — Fri, 09 Feb 2018 09:44:40 GMT

I solved with Data steps.

data novos;
set WORK.NEWValues;
run;

data DIST_DDC.List;
modify DIST_DDC.List;
	by Tag;
	Último_Disparo= MAX(Último_Disparo, Último_Disparo_new);
run;

Only modify the new values with a condition MAX.

Thanks for your attention.