Update table code

[chirumalla]

Hi,

Need update values in output data set. please help me

ONE                    TWO

X  Y  Z                X  Y  Z

1       3                1   2  

1                         1   3  4

THREE

X  Y  Z

1  2  3

1  3  4

By vairable X. 

Thanks.

[Kurt_Bremser]

Since you have multiple appearances of same values for X in both datasets, how can the corresponding records be determined?

[LinusH]

technically, this can be handled by a data step merge - given the current sort order of the input data sets.

But taht is not a reliable method. Ideally you need key variable(s), as @Kurt_Bremser hinted.

[RW9]

proc sql;
  create table WANT as
  select  A.X,
             COALESCE(A.Y,B.Y) as Y,
             COALESCE(A.Z,B.Z)  as Z
  from    HAVE1 A
  full join HAVE2 B
  on        A.X=B.X;
quit;

This assumes that X is the matching variable.  You could also use the update part of datastep:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000202975.htm

[Ksharp]

If  these two datasets have different number of obs for the same X, what you gonna do ?

data one;
input X  Y  Z  ;       
cards;
1   .    3               
1   .   . 
;
run;                    

data two;
input X  Y  Z  ;       
cards;
1   2 .
1   3  4
;
run;
proc iml;
use one;
read all var _num_ into one[c=vnames];
close;
use two;
read all var _num_ into two;
close;
x=choose(one=.,two,one);
create want from x[c=vnames];
append from x;
close;
quit;

[Astounding]

I would start by adding an arbitrary counter for matching purposes:

data one_plus;

set one;

by X;

if first.X then counter=1;

else counter + 1;

run;

data two_plus;

set two;

by X;

if first.X then counter=1;

else counter + 1;

run;

Then combining the data sets is easy:

data want;

update one_plus two_plus;

by X counter;

run;

If you have further requirements (what should happen if ONE contains 2 observations, and TWO contains three observations for the same X value?) you would have to spell out what the result should look like.