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
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;
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,
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.
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;
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.
I should have requested this earlier:
Please post your log; copy/paste it into a window opened with this button:
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
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.*
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?
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;
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;
Many Thanks @Renoux
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.