Help to fill this column

[Anto180788]

Good morning, 

I have problem. I need  to fill the column E_T99_X0010 with E_DESCRIZIONE when E_T99_C0010 = E_DOMINIO 

I have written this sql code, but it doesn't work:

proc sql;
create table _3006_AG_E_DM_T099_INDOTHX_PROVA as
select a.* ,
case when a.E_T99_C0010=b.E_DOMINIO then b.E_DESCRIZIONE
else b.E_DESCRIZIONE end as E_T99_X0010
from mis2dm._3006_ag_e_dm_t099_indothx a
left join DOMINI_LDR_FIX b
on a.E_PU_08910=b.E_PU_08910 and a.E_T99_C0010=b.E_DOMINIO ;
quit;

Many Thanks 

[Renoux]

Is this what you try to do ?

proc sql;

create table _3006_AG_E_DM_T099_INDOTHX_PROVA as
select a.* ,
b.E_DESCRIZIONE as E_T99_X0010
from mis2dm._3006_ag_e_dm_t099_indothx a
left join DOMINI_LDR_FIX b
on a.E_PU_08910=b.E_PU_08910 and a.E_T99_C0010=b.E_DOMINIO ;
quit;

Regards,

[Kurt_Bremser]

case
  when a.E_T99_C0010=b.E_DOMINIO
  then b.E_DESCRIZIONE
  else b.E_DESCRIZIONE
end as E_T99_X0010

Since you set it to E_DESCRIZIONE anyway in both branches, the CASE is not needed. Or you have a logic mistake here.

PS since you have the condition also in your ON clause, the CASE is not necessary at all. @Renoux already shows you what to do.

[Anto180788]

Many thanks @Renoux  @Kurt_Bremser 

However, it doesen't work either:

proc sql;
create table _3006_AG_E_DM_T099_INDOTHX_PROVA as
select a.* ,b.E_DESCRIZIONE as E_T99_X0010
from mis2dm._3006_ag_e_dm_t099_indothx a
left join DOMINI_LDR_FIX b
on a.E_PU_08910=b.E_PU_08910 and a.E_T99_C0010=b.E_DOMINIO ;
quit;

[Kurt_Bremser]

Please supply example data for both tables in data steps with datalines; we can't test code with pictures.

How the code works is simply demonstrated like this:

data a;
input E_PU_08910 E_T99_C0010;
datalines;
1 1
2 2
3 3
;

data b;
input E_PU_08910 E_DOMINIO E_DESCRIZIONE $;
datalines;
1 1 X
2 1 X
3 3 X
;

proc sql;
create table want as
  select a.* ,b.E_DESCRIZIONE as E_T99_X0010
  from a left join b
  on a.E_PU_08910=b.E_PU_08910 and a.E_T99_C0010=b.E_DOMINIO
;
quit;

Please explain where the result does not meet your expectations.

[Anto180788]

Kurt,
For me it's ok. However, as the code copied here, I tried to run the code that @Renoux said, but SAS doesn't fill the column. i don't know why.
What I can say, The column E_T99_X0010 already exist in ._3006_ag_e_dm_t099_indothx where I added it through Alter Table step as CHR(250).

[Kurt_Bremser]

I should have requested this earlier:

Please post your log; copy/paste it into a window opened with this button:

[Anto180788]

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 _3006_AG_E_DM_T099_INDOTHX_PROVA  as
30         select a.* ,b.E_DOMINIO,b.E_DESCRIZIONE as E_T99_X0010
31         from mis2dm._3006_ag_e_dm_t099_indothx  a
32         left join DOMINI_LDR_FIX  b
33         on a.E_PU_08910=b.E_PU_08910 and a.E_T99_C0010=b.E_DOMINIO ;
WARNING: Variable E_T99_X0010 already exists on file WORK._3006_AG_E_DM_T099_INDOTHX_PROVA.
NOTE: Table WORK._3006_AG_E_DM_T099_INDOTHX_PROVA created, with 13 rows and 11 columns.

34         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              14559.31k
      OS Memory           36176.00k
      Timestamp           19/07/2022 01:18:28 p.
      Step Count                        87  Switch Count  2
      Page Faults                       0
      Page Reclaims                     940
      Page Swaps                        0
      Voluntary Context Switches        132
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           1952
      

35         
36         %LET _CLIENTTASKLABEL=;
2                                                          The SAS System                               08:06 Tuesday, July 19, 2022

37         %LET _CLIENTPROCESSFLOWNAME=;
38         %LET _CLIENTPROJECTPATH=;
39         %LET _CLIENTPROJECTPATHHOST=;
40         %LET _CLIENTPROJECTNAME=;
41         %LET _SASPROGRAMFILE=;
42         %LET _SASPROGRAMFILEHOST=;
43         
44         ;*';*";*/;quit;run;
45         ODS _ALL_ CLOSE;
46         
47         
48         QUIT; RUN;
49         

This is the log

[Kurt_Bremser]

This is your key:

WARNING: Variable E_T99_X0010 already exists on file WORK._3006_AG_E_DM_T099_INDOTHX_PROVA.

Since the variable already exists, it prevents you from setting the values.

Drop it:

from mis2dm._3006_ag_e_dm_t099_indothx  (drop=E_T99_X0010) a

or use a comprehensive list of variables from alias a instead of a.*

[Anto180788]

Thank you @Kurt_Bremser 

Reneaming the column could be a fast solution. 

However, I have one more requirement.

I have to satisfy these conditions:

1) When E_T99_C0010= E_Dominio then  E_T99_X0010=E_DESCRIZIONE (it is possibile reneaming the E_DESCRIZIONI)

2) In other cases  E_T99_X0010=E_T99_C0010

these are objectives.

Questions:

1) Case when statement is executes before or after join?

[Renoux]

Could you keep E_T99_C0010, E_Dominio and E_DESCRIZIONE in your proc sql?
Then you could run the following data step:

data _3006_AG_E_DM_T099_INDOTHX_PROVA;
set _3006_AG_E_DM_T099_INDOTHX_PROVA;
if (E_T99_C0010= E_Dominio) then  E_T99_X0010=E_DESCRIZIONE;
else E_T99_X0010=E_T99_C0010;
/*drop whatever_you_no_longer_need;*/ 
run;

[Anto180788]

I have already thought about data step.
I would try to do everything with one proc sql step.
May Update statement ca be more useful in this case?

[Anto180788]

I resolved this issues :

proc sql;
create table _3006_AG_E_DM_T031_INDOTHX_PROVA as
select a.* ,
case when E_T99_C0010=E_DOMINIO then E_DESCRIZIONE
else E_T99_C0010 end as E_T99_X0010
from mis2dm._3006_AG_E_DM_T031_INDOTHX a
left join DOMINI_LDR_FIX b
on a.E_PU_08910=b.E_PU_08910 and a.E_T99_C0010=b.E_DOMINIO ;
quit;

It works and It results the output expected
Many Thanks for all your help

[Renoux · Accepted solution]

I am fond of the COALESCEC (for character variable) and COALESCE (for numeric variable) functions.

proc sql;
create table _3006_AG_E_DM_T099_INDOTHX_PROVA as
select a.* ,
coalescec(b.E_DESCRIZIONE,c.E_T99_C0010) format=$32. as E_T99_X0010
from mis2dm._3006_ag_e_dm_t099_indothx a
left join DOMINI_LDR_FIX b
on a.E_PU_08910=b.E_PU_08910 and a.E_T99_C0010=b.E_DOMINIO
left join DOMINI_LDR_FIX c
on a.E_PU_08910=c.E_PU_08910;
quit;

[Anto180788]

Many Thanks @Renoux