BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aleixo
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
ballardw
Super User

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?

Aleixo
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Aleixo
Quartz | Level 8

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

Aleixo
Quartz | Level 8

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.

Aleixo
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Aleixo
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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