topic Re: Help to fill this column in SAS Programming

Help to fill this column

Anto180788 — Tue, 19 Jul 2022 12:17:57 GMT

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

Re: Help to fill this column

Renoux — Tue, 19 Jul 2022 12:30:44 GMT

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,

Re: Help to fill this column

Kurt_Bremser — Tue, 19 Jul 2022 12:32:58 GMT

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.

Re: Help to fill this column

Anto180788 — Tue, 19 Jul 2022 12:46:05 GMT

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;

Re: Help to fill this column

Kurt_Bremser — Tue, 19 Jul 2022 12:56:14 GMT

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.

Re: Help to fill this column

Anto180788 — Tue, 19 Jul 2022 13:06:18 GMT

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).

Re: Help to fill this column

Kurt_Bremser — Tue, 19 Jul 2022 13:10:39 GMT

I should have requested this earlier:

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

Re: Help to fill this column

Anto180788 — Tue, 19 Jul 2022 13:22:58 GMT

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

Re: Help to fill this column

Kurt_Bremser — Tue, 19 Jul 2022 13:28:03 GMT

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.*

Re: Help to fill this column

Anto180788 — Tue, 19 Jul 2022 13:54:50 GMT

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?

Re: Help to fill this column

Renoux — Tue, 19 Jul 2022 14:00:31 GMT

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;

Re: Help to fill this column

Anto180788 — Tue, 19 Jul 2022 14:24:59 GMT

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?

Re: Help to fill this column

Anto180788 — Tue, 19 Jul 2022 14:50:06 GMT

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

Re: Help to fill this column

Renoux — Tue, 19 Jul 2022 14:50:58 GMT

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;

Re: Help to fill this column

Anto180788 — Tue, 19 Jul 2022 15:41:23 GMT

Many Thanks @Renoux

Re: Help to fill this column

Anto180788 — Wed, 20 Jul 2022 07:45:44 GMT

Good morning @Renoux @Kurt_Bremser

I have understood why the code works. Pratically, I read a dataset where the column E_T99_X0010 doesn't exist.

However, I would find a way to insert new values ( came out from a join) in a column altready existing in one step (I know that I can do this with two steps).
The following code is another new column to create but the scope is the same:
proc sql;
create table AG_E_DM_T031_INDOTHX_PROVA as
select a.*,
case when E_T031_C0010=E_DOMINIO then E_DESCRIZIONE
else E_T031_C0010 end as E_T031_X0010
from mis2dm.AG_E_DM_T031_INDOTHX a
left join DOMINI_LDR_FIX b
on a.E_PU_08910=b.E_PU_08910 and a.E_T031_C0010=b.E_DOMINIO ;
quit;

this is the log

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 AG_E_DM_T031_INDOTHX_PROVA as
30         select a.*,
31         case when E_T031_C0010=E_DOMINIO then E_DESCRIZIONE
32         else E_T031_C0010 end as E_T031_X0010
33         from mis2dm.AG_E_DM_T031_INDOTHX  a
34         left join DOMINI_LDR_FIX  b
35         on a.E_PU_08910=b.E_PU_08910 and a.E_T031_C0010=b.E_DOMINIO ;
WARNING: Variable E_T031_X0010 already exists on file WORK.AG_E_DM_T031_INDOTHX_PROVA.
NOTE: Table WORK.AG_E_DM_T031_INDOTHX_PROVA created, with 34 rows and 59 columns.

36         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              14998.01k
      OS Memory           34392.00k
      Timestamp           20/07/2022 07:32:52 m.
      Step Count                        13  Switch Count  2
      Page Faults                       0
      Page Reclaims                     1051
      Page Swaps                        0
      Voluntary Context Switches        139
      Involuntary Context Switches      5
      Block Input Operations            0
      Block Output Operations           2088

Question: is there a way to update a column already existing in a table through join?

Many Thanks

Re: Help to fill this column

Kurt_Bremser — Wed, 20 Jul 2022 09:05:48 GMT

I see I have to repeat myself.

Do NOT (as in NOT) use the asterisk. The asterisk includes the existing variable and prevents the creation of a new one with the same name.

Do this instead:

proc sql;
create table AG_E_DM_T031_INDOTHX_PROVA as
  select
    /* list of all variables except E_T031_X0010 */
    ,coalesce(b.E_DESCRIZIONE,a.E_T031_C0010) as E_T031_X0010
  from mis2dm.AG_E_DM_T031_INDOTHX a
  left join DOMINI_LDR_FIX b
  on a.E_PU_08910 = b.E_PU_08910 and a.E_T031_C0010 = b.E_DOMINIO
;
quit;

The second part of the ON clause makes the CASE unnecessary, but you still must make sure that the original value prevails if no match is found, therefore the COALESCE function. If no match is found, E_DESCRIZIONE is missing and the original value is kept.

Big hint for the future: refrain from using the asterisk. Only use it if you do not create any new variables, or can be positively sure that the new variable(s) do not exist already.

Re: Help to fill this column

Anto180788 — Wed, 20 Jul 2022 09:19:57 GMT

Good morning Kurt,
thank you for your helping explanation.
I will keep in mind your advice.
The reason of this problem is that I have a database in my company.
They said me that I have to do these solutions through updating existing columns (which are added through alter table step).
So, the problem is how I can update these.

Many thanks