topic Re: update base table using multi join in SAS Procedures

update base table using multi join

NN — Thu, 26 Apr 2012 16:44:04 GMT

Hi,

I have a base table of a million or above rows which has 4 variables as provided below.

i wish to update the date and name column in the base table using the values provided in the small_table. The join is to be on the ID and the SUB_ID variables.

My base table has a composite index defined on it hence i would require a update solution which would not delete the index.

Could someone guide me in the correct direction.

DATA BASE_TABLE;

INFILE DATALINES DELIMITER = '|';

INPUT ID1 : $10. SUB_ID : $10. NAME : $30. DATE : DATE9.

DATALINES;

AA|123|NAME1|01JAN2012

AA|134|NAME2|01JAN2012

AB|123|NAME3|01JAN2012

AA|145|NAME4|01JAN2012

;

RUN;

DATA SMALL_TABLE;

INPUT ID1 : $10. SUB_ID : $10. NAME : $30. DATE : DATE9.

DATALINES;

AA|134|NAME6|01MAR2012

AB|123|NAME7|10MAR2012

;

RUN;

Re: update base table using multi join

PGStats — Thu, 26 Apr 2012 19:16:08 GMT

Two methods that will maintain the index :

proc sql;
select * from base;
update base as B
set name=(select name from small as S where B.ID1=S.ID1 and B.SUB_ID=S.SUB_ID),
date=(select date from small as S where B.ID1=S.ID1 and B.SUB_ID=S.SUB_ID)
where exists (select * from small as S where B.ID1=S.ID1 and B.SUB_ID=S.SUB_ID);
select * from base;
quit;

data base;
set small(rename=(name=newName date=newDate));
modify base key=myIndex;
name=newname;
date=newDate;
if _iorc_=0 then replace;
run;
proc print data=base; run;

Re: update base table using multi join

NN — Sun, 29 Apr 2012 11:01:10 GMT

Thanks a lot...

The datastep solution works for me.. for some reason the sql process just takes too much time. however the datastep works a lot faster...not sure why..