BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
Astounding
PROC Star

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.

Narasimha_Kulkarni
Calcite | Level 5

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?

Astounding
PROC Star

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?
Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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