topic Help with Merging the data in SAS Programming

Help with Merging the data

Narasimha_Kulkarni — Tue, 11 Mar 2014 18:42:47 GMT

I have a critical requirement as mentioned in the text file attached. Could you help?

Re: Help with Merging the data

Astounding — Tue, 11 Mar 2014 19:44:10 GMT

It doesn't look like any of the original data is needed. You could build what you need from the second data set only:

data want;

set dataset_B;

by var1;

if last.var1;

run;

If there is some reason to utilize dataset_A as well (perhaps some VAR1 values appear in dataset_A but not in dataset_B), you could use:

data want;

update dataset_A dataset_B;

by var1;

run;

But now we have to imagine how the real problem differs from what is displayed in your requirements to determine why dataset_A might be needed at all.

Re: Help with Merging the data

Narasimha_Kulkarni — Wed, 12 Mar 2014 17:16:44 GMT

Hi Astounding,

Thank you for the post; basically the dataset_A is allocated to an online system which is built using SAS Access Framework... There are many simple indexes on the library which contains dataset_A.

I allocated the new dataset to online system which contains the proper data but it didn't work. Now I need to overwrite the Var2-3 values in dataset_A by correct values from dataset_B.

I have updated the query text file. Could you please let me know if I can do something else to get this done?

Re: Help with Merging the data

Astounding — Wed, 12 Mar 2014 17:40:13 GMT

When you say "it didn't work", what does that mean?

What program did you run?
What result did you get?
Unless the first two questions make this obvious, why do you say it didn't work?

Re: Help with Merging the data

Tom — Wed, 12 Mar 2014 18:30:02 GMT

So your original question was:

Hi,

Could you help me with this requirement?  

Requirement:
------------
Dataset A:

Var1     Var2     Var3     ---- Variables
Pm     Am     Em     --------+
:     :     :          | 
:     :     :          |
Po     Ao     Eo           |
P1     A1     E1          | 
P2     A2     E2          |
P3     A3     E3          |---- Observations
:     :     :          |
:     :     :          |
Py     Ay     Ey          |
Pz     Az     Ez          |
:     :     :          |
:     :     :          |
Pn     An     En     --------+


Values from Am to Ao, Em to Eo, Az to An and Ez to En are Correct. Values A1-Ay E1-Ey are incorrect.

I have a dataset B as below:

Var1     Var2     Var3     ---- Variables
Pm     Am     Em     --------+
:     :     :          | 
:     :     :          |
Po     Ao     Eo           |
P1     A1     E1          | 
P1     A1C     E1C          | 
P2     A2     E2          |
P2     A2C     E2C          |
P3     A3     E3          |---- Observations
P3     A3C     E3C          |
:     :     :          |
:     :     :          |
Py     Ay     Ey          |
Py     AyC     EyC          |
Pz     Az     Ez          |
:     :     :          |
:     :     :          |
Pn     An     En     --------+

I want to overwrite dataset A using the dataset B as below (I’m expecting the dataset A to look like below after fix).

Dataset A:

Var1     Var2     Var3     ---- Variables
Pm     Am     Em     --------+
:     :     :          | 
:     :     :          |
Po     Ao     Eo           |
P1     A1C     E1C          | 
P2     A2C     E2C          |
P3     A3C     E3C          |---- Observations
:     :     :          |
:     :     :          |
PyC     AyC     EyC          |
Pz     Az     Ez          |
:     :     :          |
:     :     :          |
Pn     An     En     --------+

It is not clear how you know which observations in A to update? Normally there are key variables that you could use to know which rows to update.

What code did you try?

What result did you get? Did you get any errors?

If the data for both A and B are already in your remote database then you would be better off asking your Data Base Administrator how to code the update in the native language of the database. You can use the EXECUTE statement in PROC SQL to execute code in the remote database.