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
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.