Good morning everyone
I would find a way to insert new values ( came out from a join) in a column altready existing in one step (I know that I can do this with two steps).
I have altready add the new column with alter table step:
proc sql;
alter table Mis2dm._3006_AG_E_DM_T031_INDOTHX
add E_T031_X0010 char(250);
quit;
I would update values of E_T031_X0010 column throught this join :
proc sql;
create table AG_E_DM_T031_INDOTHX_PROVA as
select a.*,
case when E_T031_C0010=E_DOMINIO then E_DESCRIZIONE
else E_T031_C0010 end as E_T031_X0010
from mis2dm.AG_E_DM_T031_INDOTHX a
left join DOMINI_LDR_FIX b
on a.E_PU_08910=b.E_PU_08910 and a.E_T031_C0010=b.E_DOMINIO ;
quit;
This is the log:
1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Aggiunta campi con sigla X'; 4 %LET _CLIENTPROCESSFLOWNAME='Flusso dei processi'; 5 %LET _CLIENTPROJECTPATH='C:\Users\AntonioCiccaglione\Desktop\Iccrea\Implementazione colonne X e aggiunta descrizione.egp' 5 ! ; 6 %LET _CLIENTPROJECTPATHHOST='LAPTOP-5ML4ES7K'; 7 %LET _CLIENTPROJECTNAME='Implementazione colonne X e aggiunta descrizione.egp'; 8 %LET _SASPROGRAMFILE=''; 9 %LET _SASPROGRAMFILEHOST=''; 10 11 ODS _ALL_ CLOSE; 12 OPTIONS DEV=SVG; 13 GOPTIONS XPIXELS=0 YPIXELS=0; 14 %macro HTML5AccessibleGraphSupported; 15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH; 16 %mend; 17 FILENAME EGHTML TEMP; 18 ODS HTML5(ID=EGHTML) FILE=EGHTML 19 OPTIONS(BITMAP_MODE='INLINE') 20 %HTML5AccessibleGraphSupported 21 ENCODING='utf-8' 22 STYLE=HtmlBlue 23 NOGTITLE 24 NOGFOOTNOTE 25 GPATH=&sasworklocation 26 ; NOTE: Writing HTML5(EGHTML) Body file: EGHTML 27 28 proc sql; 29 create table AG_E_DM_T031_INDOTHX_PROVA as 30 select a.*, 31 case when E_T031_C0010=E_DOMINIO then E_DESCRIZIONE 32 else E_T031_C0010 end as E_T031_X0010 33 from mis2dm.AG_E_DM_T031_INDOTHX a 34 left join DOMINI_LDR_FIX b 35 on a.E_PU_08910=b.E_PU_08910 and a.E_T031_C0010=b.E_DOMINIO ; WARNING: Variable E_T031_X0010 already exists on file WORK.AG_E_DM_T031_INDOTHX_PROVA. NOTE: Table WORK.AG_E_DM_T031_INDOTHX_PROVA created, with 34 rows and 59 columns. 36 37 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.02 seconds memory 15025.59k OS Memory 34392.00k Timestamp 20/07/2022 08:53:41 m. Step Count 21 Switch Count 2 Page Faults 0 Page Reclaims 1059 Page Swaps 0 Voluntary Context Switches 137 Involuntary Context Switches 1 Block Input Operations 0 Block Output Operations 2080
Many Thanks
@Kurt_Bremser already gave the answer in the original thread: https://communities.sas.com/t5/SAS-Programming/Help-to-fill-this-column/m-p/824113
@Anto180788 wrote:
HI andreas,
Kurt said to drop the column in order to add the new column with values.
My objective is to update a column, in order to keep its position in the table.
Dropping the new column and adding new one by join, it will result at the end of table.
This is a complete non-argument, for several reasons.
First, ALTER TABLE (as is used in your example where this is shown) will also add the column at the end of the observations, so dropping and re-adding it will not change anything.
Second, variables are always addressed by name, not by position, so the position within observations is irrelevant.
Third, using a comprehensive list in the SQL SELECT allows you to specify the position of every variable, if that is of importance to you.
Since you create a dataset in WORK, the SQL UPDATE statement cannot be used (which is the proper way to update existing columns in SQL).
You can add the LENGTH=250 specification in the SQL I provided in your other thread, but you CANNOT update an existing column in the way you tried here. It simply is not possible.
To be honest, I would not use SQL for this anyway. I'd either run a MERGE in a data step, or (even better) use a hash object:
data AG_E_DM_T031_INDOTHX_PROVA;
set mis2dm.AG_E_DM_T031_INDOTHX;
if _n_ =1
then do;
declare hash b (dataset:"DOMINI_LDR_FIX (rename=(E_DOMINIO=E_T031_C0010 E_DESCRIZIONE=E_T031_X0010))");
b.definekey("E_PU_08910","E_T031_C0010");
b.definedata("E_DESCRIZIONE");
b.definedone();
end;
rc = b.find();
drop rc;
run;
Thank you so much Kurt.
1) I dont'know hash object yet, but I know you should be very carefully to use it.
If I would see the output of your code, Should I just run it?
2) According to you, given that i can't update by join, how can I update the column in 2 step.
I think I have to create a temporary table to store the new column and then update the existing column by data step.
@Anto180788 wrote:
Thank you so much Kurt.
1) I dont'know hash object yet, but I know you should be very carefully to use it.If I would see the output of your code, Should I just run it?
2) According to you, given that i can't update by join, how can I update the column in 2 step.
I think I have to create a temporary table to store the new column and then update the existing column by data step.
You have to be careful with every bit of code you do. That's a given, don't work with tools you don't know how to handle.
I have already given you the answer for 2): don't use the asterisk, create the variable anew with the same attributes set in the (unnecessary) ALTER TABLE statement.
@Kurt_Bremser wrote:
@Anto180788 wrote:Thank you so much Kurt.
1) I dont'know hash object yet, but I know you should be very carefully to use it.If I would see the output of your code, Should I just run it?
2) According to you, given that i can't update by join, how can I update the column in 2 step.
I think I have to create a temporary table to store the new column and then update the existing column by data step.You have to be careful with every bit of code you do. That's a given, don't work with tools you don't know how to handle.
I have already given you the answer for 2): don't use the asterisk, create the variable anew with the same attributes set in the (unnecessary) ALTER TABLE statement.
1) I don't think that is only a problem to know hash object in order to decide if you can or not work on a task.
If your client (or somthing else) say that want new values only updating an existing column, you can't say only no.
2) So i have to remove alter table and create new one while run a join. In this way, the new column will be in the of table.
The new variable will be where you place it in the SQL SELECT; if you put it first, it will be the first variable in an observation.
If a client (who hires me because they're less expert than me) wants to tell me which method to use to achieve the goal, I tell them (in very polite words) that they're idiots and should not interfere with the doings of grown-ups.
All that a client may tell you without disqualifying them as imbeciles is
How you get from 1 to 2 using 3 is up to you, the expert.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.