topic Re: Update table code in SAS Data Management

Update table code

chirumalla — Wed, 18 May 2016 08:19:42 GMT

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.

Re: Update table code

Kurt_Bremser — Wed, 18 May 2016 09:02:54 GMT

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

Re: Update table code

LinusH — Wed, 18 May 2016 09:08:41 GMT

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.

Re: Update table code

RW9 — Wed, 18 May 2016 09:16:21 GMT

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

Re: Update table code

Ksharp — Wed, 18 May 2016 09:39:31 GMT

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;

Re: Update table code

Astounding — Wed, 18 May 2016 10:02:26 GMT

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.