Hi,
I need to update the variable from one dataset with another variable from second dataset, I have tried Proc SQL update command. but couldn't able to get it, I'm a beginner in proc sql.
I have 2 data sets with 2 variables in it,
DATASET_A.
ID Value
1 10
1 11
1 23
2 11
2 98
2 54
3 10
3 30
4 44
DATASET_B
ID Value
1 25
2 54
3 13
Now I need to update the values of DATASET_A with DATASET_B. So the desired output will be
ID Value
1 25
1 25
1 25
2 54
2 54
2 54
3 13
3 13
4 44
I have tried with the code
proc sql;
update DATASET_A
set value=(select value from DATASET_B where DATASET_A .value=DATASET_B.value)
where ID in (select ID from DATASET_B);
quit;
Its giving me " ERROR: Subquery evaluated to more than one row."
what does it mean? Any help?
As long as you are a beginner using SQL, let's use a different but very common SAS tool.
data want;
merge dataset_a (in=in1) dataset_b (in=in2 rename=(value=value_from_b));
by id;
if in1;
if in2 then value = value_from_b;
drop value_from_b;
run;
The program relies on the data being in sorted order. If that's not the case, you'll have to sort first.
For Proc SQL here an example with explanation how this needs to look like with the SAS SQL flavour.
http://support.sas.com/kb/25/216.html
It should be A.ID=B.ID. data DATASET_A; input ID Value; cards; 1 10 1 11 1 23 2 11 2 98 2 54 3 10 3 30 4 44 ; data DATASET_B; input ID Value; cards; 1 25 2 54 3 13 ; proc sql; update DATASET_A set value=(select value from DATASET_B where DATASET_A.id=DATASET_B.id) where ID in (select ID from DATASET_B); quit;
You can use the coalesce function, which takes the first non-missing value from a list of values. The list in this case is "b.value,a.value", as below. In other words, a.value is kept only when b.value is missing or absent:
proc sql;
create table want as
select coalesce(b.value,a.value) as value, *
from a left join b on a.id=b.id;
quit;
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.