BookmarkSubscribeRSS Feed
JithinJoe
Fluorite | Level 6

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?

 

4 REPLIES 4
Astounding
PROC Star

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.

Patrick
Opal | Level 21

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

 

Ksharp
Super User
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;
 

mkeintz
PROC Star

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;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 493 views
  • 2 likes
  • 5 in conversation