Hi @bobsas1
Here it is
/* Declare recursive macro to construct the sequence of ID changes */
%macro getChanges(id,outDs,allIdsDs);
%local oldid date;
%let oldid=;
%let date=;
data child;
set &allIdsDs(where=(newid=&id));
call symputx('oldid', oldid);
call symputx('date',changedate);
run;
%if %length(&oldid)>0 %then
%do;
PROC APPEND BASE=&outDs DATA=child; RUN;
%let changes=&changes/&oldid:&date;
%getChanges(&oldid,&outDs,&allIdsDs);
%end;
%mend getChanges;
%global changes;
%macro getIdChangeHistory(
p_id= /* ID value to extract change history for */
, p_asOfDate= %sysfunc(day()) /* SAS date. Default: Current date */
, p_idsDsName= /* Data set name of all existing IDs */
, p_outSmryDsName= /* Output Data set of changes summary */
, p_outChngHistDsName= /* Output Data set of changes records */
);
/* Create two data sets with single record in each */
DATA
&p_outSmryDsName(KEEP=asof origid currid changecount)
&p_outChngHistDsName(KEEP=oldid newid changedate);
LENGTH asof 8 origid currid 4 changecount 3;
FORMAT asof mmddyy10.;
/* Read change record */
SET &p_idsDsName(where=(newid = &p_id));
/* Store change record */
OUTPUT &p_outChngHistDsName;
/* Initialize change summary fields */
origid = oldid;
currid = newid;
asof = &p_asOfDate;
changecount = .;
/* Store partial/incomplete change summary info */
OUTPUT &p_outSmryDsName;
RUN;
/* Finalize all needed output data sets */
DATA &p_outSmryDsName(DROP=rc changes);
SET &p_outSmryDsName;
call symput('changes',cats(origid,':',asof));
rc = dosubl('%getChanges('||origid||",&p_outChngHistDsName,&p_idsDsName)");
changes = symget('changes');
changecount= countc(changes,'/')+1;
origid = scan(scan(changes,-1,'/'),1,':');
run;
PROC SORT DATA=&p_outChngHistDsName;
BY oldid changedate;
RUN;
%mend getIdChangeHistory;
/* Create sample data of all available IDs and their changes over time */
data work.all_ids;
attrib oldid newid length=8 changedate informat=mmddyy10. format=mmddyy10.;
input oldid newid changedate;
datalines;
1 2 1/1/10
10 11 1/1/10
2 3 7/1/10
3 4 7/10/10
22 4 7/10/10
11 12 8/1/10
;
run;
/* New: Added to construct a table of unique IDs with change history */
proc sql;
create table work.unique_currIds AS
select distinct a.newid as currID
FROM work.all_ids a
where not exists (select 'x' from work.all_ids b where b.oldid=a.newid);
quit;
/* New: Declare new macro to get the Change history Summary for a given list of IDs */
%macro getChangeSummary(p_inCurrIdDsName=, p_varName=, p_date=, p_inAllIdsDsName=, p_outChangeSummaryDsName=);
/* Prep a file for dynamically generated code */
FILENAME dyncode TEMP;
/* Read the input data set and construct macro call for every record */
DATA _NULL_;
FILE dyncode lrecl=300;
LENGTH stmt $300 id_smry_ds id_hstry_ds $40;
SET &p_inCurrIdDsName;
/* construct the parts of the final macro call */
id_smry_ds = CATX('_','work.',&p_varName,'smry');
id_hstry_ds = CATX('_','work.',&p_varName,'changeHistory');
/* put together the macro call and write it out */
stmt =CATS('%getIdChangeHistory(p_id=',&p_varName,",p_asOfDate=&p_date , p_idsDsName=&p_inAllIdsDsName ,");
stmt =CATS(stmt,'p_outSmryDsName=',id_smry_ds,', p_outChngHistDsName=',id_hstry_ds,');');
PUT stmt;
PUTLOG 'NOTE: writing out the following macro call:' stmt ;
/* Write out Proc Append statement to construct single Changes Summary data set */
id_smry_ds = STRIP(id_smry_ds);
put "PROC APPEND BASE=&p_outChangeSummaryDsName DATA=" id_smry_ds "; RUN;";
RUN;
/* Execute the dynamic code */
%include dyncode;
;;;;
%mend getChangeSummary;
%getChangeSummary(p_inCurrIdDsName=work.unique_currIds
, p_varName=currID
, p_date='15Aug2010'd
, p_inAllIdsDsName=work.all_ids
, p_outChangeSummaryDsName=work.IdsChangeSummary)
All was needed is this new macro
/* New: Declare new macro to get the Change history Summary for a given list of IDs */
%macro getChangeSummary(p_inCurrIdDsName=, p_varName=, p_date=, p_inAllIdsDsName=, p_outChangeSummaryDsName=);
/* Prep a file for dynamically generated code */
FILENAME dyncode TEMP;
/* Read the input data set and construct macro call for every record */
DATA _NULL_;
FILE dyncode lrecl=300;
LENGTH stmt $300 id_smry_ds id_hstry_ds $40;
SET &p_inCurrIdDsName;
/* construct the parts of the final macro call */
id_smry_ds = CATX('_','work.',&p_varName,'smry');
id_hstry_ds = CATX('_','work.',&p_varName,'changeHistory');
/* put together the macro call and write it out */
stmt =CATS('%getIdChangeHistory(p_id=',&p_varName,",p_asOfDate=&p_date , p_idsDsName=&p_inAllIdsDsName ,");
stmt =CATS(stmt,'p_outSmryDsName=',id_smry_ds,', p_outChngHistDsName=',id_hstry_ds,');');
PUT stmt;
PUTLOG 'NOTE: writing out the following macro call:' stmt ;
/* Write out Proc Append statement to construct single Changes Summary data set */
id_smry_ds = STRIP(id_smry_ds);
put "PROC APPEND BASE=&p_outChangeSummaryDsName DATA=" id_smry_ds "; RUN;";
RUN;
/* Execute the dynamic code */
%include dyncode;
;;;;
%mend getChangeSummary;
Which is really nothing more than a wrapper for constructing the %getIdChangeHistory (....) macro call.
Check out the SAS log to see what I'm talking about.
Hope this helps
