CODEWITHSUNDEEP
sas
user2146441
user2146441

Reputation: 228

Update the values of a column in a dataset with another table

If I have Table A with two columns: ID and Mean, and Table B with a long list of columns including Mean, how can I replace the values of the Mean column in Table B with the IDs that exist in Table A?

I've tried PROC SQL UPDATE and both DATASET MERGE and DATASET UPDATE but they keep adding rows when the number of columns is not equal in both tables.

Upvotes: 0

Views: 6163

Answers (1)

Joe
Joe

Reputation: 63434

data want;
merge have1(in=H1) have2(in=H2);
by mergevar;
if H1;
run;

That will guarantee that H2 does not add any rows, unless there are duplicate values for one of the by values. Other conditions can be used as well; if h2; would do about the same thing for the right-hand dataset, and if h1 and h2; would only keep records that come from both tables.

PROC SQL join should also work fairly easily.

proc sql;
 create table want as
 select A.id, coalesce(B.mean, A.mean)
 from A left join B
 on A.id=B.id;
quit;

Upvotes: 2

Related Questions