topic Re: Update in New SAS User

Update

SASPhile — Fri, 25 Jan 2019 16:09:05 GMT

update :

TableA
Id Value
1 abc
2 pqr

TableB
Id Value
1
2 pqr

how to update tableb for an id if value is missing, update with valur from tableA?

Final output:

TableB
Id Value
1 abc
2 pqr

Re: Update

novinosrin — Fri, 25 Jan 2019 16:14:47 GMT

data a;
input id value $;
cards;
1 abc
2 pqr
;
data b;
input id value $;
cards;
1 .
2 pqr
;
data b;
  modify b a;
  by id;
  if _IORC_=0 then replace;
  else _ERROR_=0;
run;

Re: Update

SASPhile — Fri, 25 Jan 2019 16:23:30 GMT

if there are multiple columns and only one column is to be updated, like below:

I dont want score to be replaced. only value ,the below logic doesnt work,right?

data a;
input id value $ score;
cards;
1 abc 350
2 pqr 275
;
data b;
input id value $3-6 score ;
cards;
1 371
2 pqr 372
;
run;
data b;
modify b a;
by id;
if _IORC_=0 then replace;
else _ERROR_=0;
run;

Re: Update

VDD — Fri, 25 Jan 2019 16:27:54 GMT

@SASPhile please post a better sample dataset that represents your needs. You are piece mealing your request. Don't play the manager role in requesting results please.

Re: Update

Astounding — Fri, 25 Jan 2019 16:36:22 GMT

If TableB never has multiple observations for the same ID, you could use:

data want;

update b (in=keepme) a;

by id;

if keepme;

run;

Re: Update

novinosrin — Fri, 25 Jan 2019 16:39:27 GMT


data a;
input id value $ score;
cards;
1 abc 350
2 pqr 275
;
data b;
input id value $ score ;
cards;
1 . 371 
2 pqr 372
;

proc sql;
update b
SET value = (SELECT value
                  FROM a
                  WHERE b.id = a.id)
WHERE value=' ';
quit;

Re: Update

novinosrin — Fri, 25 Jan 2019 16:43:26 GMT

And as a matter of fact, you can push the proc sql update query in-database and have it done at that level rather than at SAS level.

Re: Update

novinosrin — Fri, 25 Jan 2019 16:48:53 GMT

Assuming you are just updating value sourcing from Table A, you could use dataset option to keep id and value alone

data b;
  modify b a(keep=id value);/*drop the rest*/
  by id;
  if _IORC_=0  then replace;
  else _ERROR_=0;
run;