Merge Question

[KevinC_]

Hello Everyone,

I am merging two datasets: old and new.   Dataset old has historical data from prior dates and contains 900 columns.  Dataset new has 800 columns and contains new data for today, and hese 800 columns also exist in OLD.  I would like to update the common 800 columns in OLD with value from NEW and keep the other 100 columns in OLD as is where cust_id is in both OLD and NEW.    The code below attemps to do that.  But I couldn't possibly list all 800 columns in the data step.  See the sample data below.  Dataset OUT1 has the 1st 100 columns from OLD and columns 101 to column 900 are populated with value from NEW.  I hope this makes sense.  Does anyone have any suggestions?

data out1;

merge old (in=a) new (in=b);

by cust_id;

if a or b;

if a = 0 and b = 1 then col1 = new_col1;

if a = 0 and b = 1 then col2 = new_col2;

..

run;

Dataset OLD:

cust_id col1 col2 col3 ...col100, col101, col102,..  col898, col899, col 900

111111   aa   bb   cc ....   dd       gg         kk          ff         ll          kk

222222   bb   cc    dd ....   bc        ee         nn          qq        tt         gg

Dataset NEW:

cust_id col1 col2... col798, col799, col800

111111 uu   ww....   xx       yy       zz

Dataset OUT1:

cust_id col1 col2 col3 ... col100, col101, col102,..  col898, col899, col900

111111  aa    bb   cc....    dd        uu      ww....     xx       yy       zz

[Astounding]

It looks like you are trying for this combination:

data out1;
   update old new;

   by cust_id;

run;

Read up a little bit on UPDATE ... it's probably the right tool for the job.

[Haikuo]

Hi,

I would assume that your cust_id is unique, and you want variable in NEW col1-col800 to replace those of col101-col900 in old, if that is the case, all you need to do is to rename the incoming variables from NEW during the merge: (the caveat of this approach is the total length of macro variable is only 32k)

proc sql;

  select cats(name,'=',cats(compress(name,,'ka'),input(compress(name,,'kd'),best.)+100)) into :name separated by ' '

  from dictionary.columns where libname='YOURLIB'/*NEED TO BE CAPPED*/ AND MEMNAME='NEW' /*CAPPED*/

  and name contains 'col';

QUIT;

data out1;

  merge old new(rename=(&name));

  by cust_id;

run;

Haikuo

[ArtC]

Starting with SAS9 the size of macro variables has been increased to 64K.

[Haikuo]

Thanks for the update, Art!

Haikuo