SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Update table code

Reply
Contributor
Posts: 41

Update table code

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.

 

 

 

 

 

 

             

Super User
Posts: 6,972

Re: Update table code

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,260

Re: Update table code

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 @KurtBremser hinted.

Data never sleeps
Super User
Super User
Posts: 7,430

Re: Update table code

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

Super User
Posts: 9,691

Re: Update table code

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;
Super User
Posts: 5,099

Re: Update table code

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.

Ask a Question
Discussion stats
  • 5 replies
  • 334 views
  • 0 likes
  • 6 in conversation