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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.