Update only new values with conditions

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

Update only new values with conditions

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

 


Accepted Solutions
Solution
‎02-09-2018 04:41 AM
Super User
Super User
Posts: 8,969

Re: Update only new values with conditions

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;

View solution in original post


All Replies
Super User
Posts: 12,676

Re: Update only new values with conditions

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?

Contributor
Posts: 51

Re: Update only new values with conditions

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

Solution
‎02-09-2018 04:41 AM
Super User
Super User
Posts: 8,969

Re: Update only new values with conditions

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;
Contributor
Posts: 51

Re: Update only new values with conditions

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

Contributor
Posts: 51

Re: Update only new values with conditions

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.

Contributor
Posts: 51

Re: Update only new values with conditions

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

Super User
Super User
Posts: 8,969

Re: Update only new values with conditions

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.

Contributor
Posts: 51

Re: Update only new values with conditions

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 110 views
  • 0 likes
  • 3 in conversation