Help with Merging the data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Help with Merging the data

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

Attachment

Accepted Solutions
Solution
‎03-11-2014 03:44 PM
Super User
Posts: 5,080

Re: Help with Merging the data

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.

View solution in original post


All Replies
Solution
‎03-11-2014 03:44 PM
Super User
Posts: 5,080

Re: Help with Merging the data

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.

Occasional Contributor
Posts: 12

Re: Help with Merging the data

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?

Super User
Posts: 5,080

Re: Help with Merging the data

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?
Super User
Super User
Posts: 6,499

Re: Help with Merging the data

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 238 views
  • 0 likes
  • 3 in conversation