topic Re: Update Table A (that has more rows per key) with table B that has only ONE row per key. in SAS Programming

Update Table A (that has more rows per key) with table B that has only ONE row per key.

Longimanus — Wed, 28 Feb 2024 11:05:09 GMT

To all SAS Code Guru's out there! 🙂 This should be pretty easy (I can do this in other languages easily) but I can't do it in SAS.
Never tried it. Before I spend time looking it up I'll ask here (deadline to meet). So this is what I have:

Table A has more than one row per key (key_var). This is the table I need to update with the information in Table B that only

has one row per key. Both tables are indexed and sorted.

Have Corrections Want

Table_A Table_B Table_A

Key_var Var_1 Var_2 Key_var Fix_Var_1 Fix_Var_2 Key_var Var_1 Var_2

1 X A 1 XX AA 1 XX AA

2 Y B 2 YY BB 2 YY BB

2 Y B 3 ZZ CC 2 YY BB

3 Z C 3 ZZ CC

My code (which unfortunately only updates the first occurance):

data TABLE_A;

set TABLE_B;

modify TABLE_A key=key_var;

if _iorc_=0 then do;

Var_1 = Fix_var_1;

Var_2 = Fix_var_2;

replace;

end;

else _error_=0;

run;

With the above I only update the first row in Table-A that has the equivalent key in Table-B. Not the rest. Is there a simple way to alter this in the above or do I need to solve this completely different. I will keep trying to figure out HOW but like to see what you guys come up with. For a "Guru" this should be a walk in the park. Right?

This is what I get now: (not good enough as you see).

Have Corrections Want

Table_A Table_B Table_A

Key_var Var_1 Var_2 Key_var Fix_Var_1 Fix_Var_2 Key_var Var_1 Var_2

1 X A 1 XX AA 1 XX AA

2 Y B 2 YY BB 2 YY BB

2 Y B 3 ZZ CC 2 Y B <---- 😒

3 Z C 3 ZZ CC

Any suggestion? Either a fix in the above or an entire other method Thanx already now !!! 🤓

Re: Update Table A (that has more rows per key) with table B that has only ONE row per key.

Longimanus — Wed, 28 Feb 2024 12:21:00 GMT

Hi folks!

I have another way to fix this. Another method that I will try. I will join the two tables in SQL so I get each correction row in the FIX-TABLE (Table-B) to be joined to each row in Table-A. When I have all information in one table it's easy to get the result I want! 😊

That said: I am still curious how YOU'd solve this!

Have a nice day wherever you are!

Cheers!
Longimanus

Re: Update Table A (that has more rows per key) with table B that has only ONE row per key.

AhmedAl_Attar — Wed, 28 Feb 2024 13:17:54 GMT

Hi @Longimanus

Have a look at these two papers along with their references for alternative ways

- https://www.lexjansen.com/sesug/2020/SESUG2020_Paper_150_Final_PDF.pdf

- https://support.sas.com/resources/papers/proceedings15/2219-2015.pdf

Hope this helps,

Ahmed