BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Anto180788
Calcite | Level 5

Anto180788_0-1658232387698.png

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Renoux
Obsidian | Level 7

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;

 

 

View solution in original post

17 REPLIES 17
Renoux
Obsidian | Level 7

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
Super User
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
Calcite | Level 5

Many thanks @Renoux  @Kurt_Bremser 

 

However, it doesen't work either:

 

Anto180788_0-1658234741231.png

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
Super User

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
Calcite | Level 5
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).
Anto180788
Calcite | Level 5
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
Super User

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
Calcite | Level 5

Thank you @Kurt_Bremser 

 

Reneaming the column could be a fast solution. 

However, I have one more requirement.

 

Anto180788_0-1658237874458.png

 

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
Obsidian | Level 7

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
Calcite | Level 5
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
Calcite | Level 5
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
Obsidian | Level 7

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
Calcite | Level 5

Many Thanks @Renoux 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 17 replies
  • 2876 views
  • 0 likes
  • 3 in conversation