02-15-2013 02:22 PM
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?
merge old (in=a) new (in=b);
if a or b;
if a = 0 and b = 1 then col1 = new_col1;
if a = 0 and b = 1 then col2 = new_col2;
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
cust_id col1 col2... col798, col799, col800
111111 uu ww.... xx yy zz
cust_id col1 col2 col3 ... col100, col101, col102,.. col898, col899, col900
111111 aa bb cc.... dd uu ww.... xx yy zz
02-15-2013 03:23 PM
It looks like you are trying for this combination:
update old new;
Read up a little bit on UPDATE ... it's probably the right tool for the job.
02-15-2013 03:24 PM
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)
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';
merge old new(rename=(&name));