Help to fill this column

[Anto180788]

Good morning @Renoux @Kurt_Bremser 

I have understood why the code works. Pratically, I read a dataset where the column E_T99_X0010 doesn't exist. 

However, 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).
The following code is another new column to create but the scope is the same:
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         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      user cpu time       0.02 seconds
      system cpu time     0.01 seconds
      memory              14998.01k
      OS Memory           34392.00k
      Timestamp           20/07/2022 07:32:52 m.
      Step Count                        13  Switch Count  2
      Page Faults                       0
      Page Reclaims                     1051
      Page Swaps                        0
      Voluntary Context Switches        139
      Involuntary Context Switches      5
      Block Input Operations            0
      Block Output Operations           2088
      

Question: is there a way to update a column already existing in a table through join?

Many Thanks

[Kurt_Bremser]

I see I have to repeat myself.

Do NOT (as in NOT) use the asterisk. The asterisk includes the existing variable and prevents the creation of a new one with the same name. 

Do this instead:

proc sql;
create table AG_E_DM_T031_INDOTHX_PROVA as
  select
    /* list of all variables except E_T031_X0010 */
    ,coalesce(b.E_DESCRIZIONE,a.E_T031_C0010) 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;

The second part of the ON clause makes the CASE unnecessary, but you still must make sure that the original value prevails if no match is found, therefore the COALESCE function. If no match is found, E_DESCRIZIONE is missing and the original value is kept.

Big hint for the future: refrain from using the asterisk. Only use it if you do not create any new variables, or can be positively sure that the new variable(s) do not exist already.

[Anto180788]

Good morning Kurt,
thank you for your helping explanation.
I will keep in mind your advice.
The reason of this problem is that I have a database in my company.
They said me that I have to do these solutions through updating existing columns (which are added through alter table step).
So, the problem is how I can update these.

Many thanks