topic Re: Update an existing column throught join in SAS Programming

Update an existing column throught join

Anto180788 — Wed, 20 Jul 2022 08:55:22 GMT

Good morning everyone

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

I have altready add the new column with alter table step:

proc sql;
alter table Mis2dm._3006_AG_E_DM_T031_INDOTHX
add E_T031_X0010 char(250);
quit;

I would update values of E_T031_X0010 column throught this join :

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         
37         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      user cpu time       0.00 seconds
      system cpu time     0.02 seconds
      memory              15025.59k
      OS Memory           34392.00k
      Timestamp           20/07/2022 08:53:41 m.
      Step Count                        21  Switch Count  2
      Page Faults                       0
      Page Reclaims                     1059
      Page Swaps                        0
      Voluntary Context Switches        137
      Involuntary Context Switches      1
      Block Input Operations            0
      Block Output Operations           2080

Many Thanks

Re: Update an existing column throught join

andreas_lds — Wed, 20 Jul 2022 09:20:25 GMT

@Kurt_Bremser already gave the answer in the original thread: https://communities.sas.com/t5/SAS-Programming/Help-to-fill-this-column/m-p/824113

Re: Update an existing column throught join

Kurt_Bremser — Wed, 20 Jul 2022 09:58:47 GMT

Since you create a dataset in WORK, the SQL UPDATE statement cannot be used (which is the proper way to update existing columns in SQL).

You can add the LENGTH=250 specification in the SQL I provided in your other thread, but you CANNOT update an existing column in the way you tried here. It simply is not possible.

To be honest, I would not use SQL for this anyway. I'd either run a MERGE in a data step, or (even better) use a hash object:

data AG_E_DM_T031_INDOTHX_PROVA;
set mis2dm.AG_E_DM_T031_INDOTHX;
if _n_ =1
then do;
  declare hash b (dataset:"DOMINI_LDR_FIX (rename=(E_DOMINIO=E_T031_C0010 E_DESCRIZIONE=E_T031_X0010))");
  b.definekey("E_PU_08910","E_T031_C0010");
  b.definedata("E_DESCRIZIONE");
  b.definedone();
end;
rc = b.find();
drop rc;
run;

Re: Update an existing column throught join

Anto180788 — Wed, 20 Jul 2022 12:37:29 GMT

HI andreas,
Kurt said to drop the column in order to add the new column with values.
My objective is to update a column, in order to keep its position in the table.
Dropping the new column and adding new one by join, it will result at the end of table.

Re: Update an existing column throught join

Kurt_Bremser — Wed, 20 Jul 2022 12:49:14 GMT

@Anto180788 wrote:
HI andreas,
Kurt said to drop the column in order to add the new column with values.
My objective is to update a column, in order to keep its position in the table.
Dropping the new column and adding new one by join, it will result at the end of table.

This is a complete non-argument, for several reasons.

First, ALTER TABLE (as is used in your example where this is shown) will also add the column at the end of the observations, so dropping and re-adding it will not change anything.

Second, variables are always addressed by name, not by position, so the position within observations is irrelevant.

Third, using a comprehensive list in the SQL SELECT allows you to specify the position of every variable, if that is of importance to you.

Re: Update an existing column throught join

Anto180788 — Wed, 20 Jul 2022 12:52:21 GMT

Thank you so much Kurt.
1) I dont'know hash object yet, but I know you should be very carefully to use it.

If I would see the output of your code, Should I just run it?

2) According to you, given that i can't update by join, how can I update the column in 2 step.
I think I have to create a temporary table to store the new column and then update the existing column by data step.

Re: Update an existing column throught join

Kurt_Bremser — Wed, 20 Jul 2022 13:27:47 GMT

@Anto180788 wrote:

Thank you so much Kurt.
1) I dont'know hash object yet, but I know you should be very carefully to use it.

If I would see the output of your code, Should I just run it?

2) According to you, given that i can't update by join, how can I update the column in 2 step.
I think I have to create a temporary table to store the new column and then update the existing column by data step.

You have to be careful with every bit of code you do. That's a given, don't work with tools you don't know how to handle.

I have already given you the answer for 2): don't use the asterisk, create the variable anew with the same attributes set in the (unnecessary) ALTER TABLE statement.

Re: Update an existing column throught join

Anto180788 — Wed, 20 Jul 2022 13:41:01 GMT

@Kurt_Bremser wrote:
@Anto180788 wrote:
Thank you so much Kurt.
1) I dont'know hash object yet, but I know you should be very carefully to use it.
If I would see the output of your code, Should I just run it?

2) According to you, given that i can't update by join, how can I update the column in 2 step.
I think I have to create a temporary table to store the new column and then update the existing column by data step.
You have to be careful with every bit of code you do. That's a given, don't work with tools you don't know how to handle.
I have already given you the answer for 2): don't use the asterisk, create the variable anew with the same attributes set in the (unnecessary) ALTER TABLE statement.

1) I don't think that is only a problem to know hash object in order to decide if you can or not work on a task.

If your client (or somthing else) say that want new values only updating an existing column, you can't say only no.

2) So i have to remove alter table and create new one while run a join. In this way, the new column will be in the of table.

Re: Update an existing column throught join

Kurt_Bremser — Wed, 20 Jul 2022 15:27:17 GMT

The new variable will be where you place it in the SQL SELECT; if you put it first, it will be the first variable in an observation.

Re: Update an existing column throught join

Anto180788 — Thu, 21 Jul 2022 11:01:05 GMT

Thank you very much Kurt.

Re: Update an existing column throught join

Kurt_Bremser — Thu, 21 Jul 2022 14:57:50 GMT

If a client (who hires me because they're less expert than me) wants to tell me which method to use to achieve the goal, I tell them (in very polite words) that they're idiots and should not interfere with the doings of grown-ups.

All that a client may tell you without disqualifying them as imbeciles is

we have ... (the data they start with)
we want ... (the result which should be obtained)
we can provide ... (the extent of their SAS license, the available storage, memory etc)

How you get from 1 to 2 using 3 is up to you, the expert.