topic Re: Merge Question in SAS Procedures

Merge Question

KevinC_ — Fri, 15 Feb 2013 19:22:43 GMT

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

Re: Merge Question

Astounding — Fri, 15 Feb 2013 20:23:49 GMT

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.

Re: Merge Question

Haikuo — Fri, 15 Feb 2013 20:24:28 GMT

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

Re: Merge Question

ArtC — Sun, 17 Feb 2013 06:31:43 GMT

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

Re: Merge Question

Haikuo — Tue, 19 Feb 2013 14:45:14 GMT

Thanks for the update, Art!

Haikuo