I have a critical requirement as mentioned in the text file attached. Could you help?
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.
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.
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?
When you say "it didn't work", what does that mean?
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.