/*----------------------------------------\
| Update the existing datasets; |
|-------------------------------------------|
|--------------------------------------------------------------------|
|---------------------------|
| Arguments: |
| indata - input datasets, at least 2 datasets are required; |
| by - key variables between the input datasets; |
| this is the key variables we need for merging the |
| datasets; |
| Note: these variables will be used as the complex keys;|
| keep - Keep the rows from which datasets; |
| outdata - The output dataset; |
|--------------------------------|
|--------------------------------------------------------------------|
|---------------------------------------|
| Example: |
| %update(indata=ridcrf.ae ridcrf.ae1, by=pt aedt docnum, keep=1, |
| outdata=ae); |
| Usgae: update(indata=,by=, keep=1, outdata=)/parmbuff; |
\----------------------------------------*/
%macro update/parmbuff;
/*--------------------------------------------\
| Copy Right: Duo Zhou; |
| Created: 12-01-2002 1:54pm; |
| Purpose: update existing datasets; |
\--------------------------------------------*/
%let _updatecnt_=0;
%let syspbuff=%sysfunc(translate(%quote(%substr(%quote(%trim(%quote(%left(%quote(&syspbuff))))), 2, %eval(%length(%trim(%quote(%left(%quote(&syspbuff)))))-2))), %str(%'), %str(%")));
%let indata=; %let by=; %let keep=; %let outdata=;
%do %while(%length(%nrbquote(%scan(%nrbquote(&syspbuff), %eval(&_updatecnt_+1), %nrbquote(,)))));
%let _updatecnt_=%eval(&_updatecnt_+1);
%let _udparam_=%nrbquote(%qscan(%nrbquote(&syspbuff), &_updatecnt_, %nrbquote(,)));
%let _udparam1_=%trim(%left(%qscan(%nrbquote(&_udparam_), 1, %str(=))));
%let _udparam2_=%substr(%quote(&_udparam_), %eval(%index(%quote(&_udparam_),%str(=))+1), %eval(%length(&_udparam_)-%index(%quote(&_udparam_),%str(=))));
%if (not %index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&_udparam_))))), %str(=))) %then %do;
%if (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&_udparam2_))))), %str(%()) eq 1) and
(%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(%sysfunc(reverse(&_udparam2_))))))), %str(%))) eq 1) %then
%let _udparam2_=%substr(%quote(%trim(%quote(%left(%quote(&_udparam2_))))), 2, %eval(%length(%trim(%quote(%left(%quote(&_udparam2_)))))-2));
%if (%quote(&_updatecnt_) = %quote(1)) %then %let indata=&_udparam2_;
%else %if (%quote(&_updatecnt_) = %quote(2)) %then %let by=&_udparam2_;
%else %if (%quote(&_updatecnt_) = %quote(3)) %then %do;
%if (%scan(&_udparam2_,1,1234567890)=) %then %let keep=&_udparam2_;
%else %let keep=;
%end;
%else %if (%quote(&_updatecnt_) = %quote(3)) %then %let outdata=&_udparam1_;
%end;
%else %if (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&_udparam2_))))), %str(%()) eq 1) and
(%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(%sysfunc(reverse(&_udparam2_))))))), %str(%))) eq 1) %then
%let &_udparam1_=%substr(%quote(%trim(%quote(%left(%quote(&_udparam2_))))), 2, %eval(%length(%trim(%quote(%left(%quote(&_udparam2_)))))-2));
%else %let &_udparam1_=&_udparam2_;
%end;
/* By Variables */
%if (%quote(&by)=) %then %do;
%put ==> Alert! Merging Variables are not given!;
%goto finish;
%end;
%let _updatebnt_=0; %let _updatesqlby_=;
%do %while(%length(%nrbquote(%scan(%nrbquote(&by), %eval(&_updatebnt_+1), %nrbquote( ,)))));
%let _updatebnt_=%eval(&_updatebnt_+1);
%if (%quote(&_updatebnt_)=%quote(1)) %then %let _updatesqlby_=%nrbquote(%qscan(%nrbquote(&by), &_updatebnt_, %nrbquote( ,)));
%else %let _updatesqlby_=%nrbquote(&_updatesqlby_), %nrbquote(%qscan(%nrbquote(&by), &_updatebnt_, %nrbquote( ,)));
%end;
/* Input Datasets */
%if (%quote(&indata)=) %then %do;
%put ==> Alert! No Input dataset is provided!;
%goto finish;
%end;
%else %if %index(&indata,.) = 0 %then %let indata=%upcase(work.&indata.);
%else %let indata=%upcase(&indata);
%let _updatednt_=0; %Let _updatenumdup_=0; %let _updatemergedata_=; %let _updatenvaliddata_=0;
%do %while(%length(%nrbquote(%scan(%nrbquote(&indata), %eval(&_updatednt_+1), %nrbquote( ,)))));
%let _updatednt_=%eval(&_updatednt_+1);
%let _updatedata_&_updatednt_=%nrbquote(%qscan(%nrbquote(&indata), &_updatednt_, %nrbquote( ,)));
%if (%sysfunc(exist(&&_updatedata_&_updatednt_))) or (%sysfunc(exist(&&_updatedata_&_updatednt_, VIEW))) %then %do;
%let _updatenvaliddata_=%eval(&_updatenvaliddata_+1);
options nonotes;
proc sort data=&&_updatedata_&_updatednt_ out=_updatedata_&_updatednt_; by &by; run;
options notes;
%if (&_updatednt_ = 1) %then %do;
%let _updatemergedata_=_updatedata_&_updatednt_ (in=in%trim(%left(&_updatednt_)));
%put NOTE: There were %trim(%left(%nobs(_updatedata_&_updatednt_))) observations read from data set %trim(%left(%upcase(&&_updatedata_&_updatednt_))).;
%end;
%else %do;
%let _updatemergedata_=&_updatemergedata_ _updatedata_&_updatednt_ (in=in%trim(%left(&_updatednt_)));
%put NOTE: There were %trim(%left(%nobs(_updatedata_&_updatednt_))) observations read from data set %trim(%left(%upcase(&&_updatedata_&_updatednt_))).;
%end;
options nonotes;
proc sql;
create table _updatetmp_%trim(%left(&_updatednt_)) as
select *
from &&_updatedata_&_updatednt_
group by &_updatesqlby_
having count(*)>1;
%if (%nobs(_updatetmp_%trim(%left(&_updatednt_)))) %then %Let _updatenumdup_=&_updatenumdup_+1;
proc datasets library=work nolist; delete _updatetmp_%trim(%left(&_updatednt_)); run;quit;
options notes;
%end;
%end;
%if (&_updatenumdup_ >1) %then %do;
%put ==> Alert! More than 1 dataset has duplicate records, this is many to many merge, update process failed!;
%goto finish;
%end;
%if (&_updatednt_ < 2) %then %do;
%put ==> Alert! Only 1 dataset is provided, dataset is not updated!;
%goto finish;
%end;
%if (&_updatenvaliddata_ < 2) %then %do;
%put ==> Alert! Only found 1 valid dataset, dataset is not updated!;
%goto finish;
%end;
%if (%quote(&outdata) = ) %then %let outdata=&_updatedata_1;
%else %if %index(&outdata,.) = 0 %then %let outdata=%upcase(work.&outdata.);
%else %let outdata=%upcase(&outdata);
%if (%quote(&keep) ne) %then %do;
%if (&keep < 1) or (&keep > &_updatednt_) %then %do;
%put ==> Alert! Only %trim(%left(&_updatednt_)) datasets are found, I cannot find the dataset %trim(%left(&keep)).;
%goto finish;
%end;
%end;
options nonotes;
data &outdata;
update &_updatemergedata_;
by &by;
%if (%quote(&keep) ne) %then if in%trim(%left(&keep));;
run;
proc sql;
%do i=1 %to &_updatednt_;
%if (%sysfunc(exist(_updatedata_&i))) %then drop table _updatedata_&i;;
%end;
quit;
options notes;
%put NOTE: There were %trim(%left(%nobs(&outdata))) observations write to data set %trim(%left(%upcase(&outdata))).;
%finish:
%mend update;
