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 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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