Hash Objects | Count changes to a parent ID over time and list history of related child ID's

[bobsas1]

I want to be able to input a table of parent ID's and count the number of times the ID has changed over time and also create a list of the parent ID and all the related children ID's, including the date the child ID's changed.  I'm experimenting with the data/ hash objects in the attachment.

The hash objects code does not produce output for “want”. I would like the output to show:
1). As of the current evaluation date (8/15/10), the # times the parent ID I specified (4) changed & the original ID:

asof | origid | currid  | changecount

08/15/2010   1     4     3 

AND

2). The historical series of changes related to the current parent ID (4):

changedate| oldid | newid
01/01/2010   1   2
07/01/2010   2   3
07/10/2010   3   4
I'm new to the Community, so please let me know if I should modify this post to make it easier for the Community to evaluate.  I could not find the insert SAS code tool, which may have presented better.

[AhmedAl_Attar]

Hi @bobsas1 

My approach does not involve Hash Object, but I was interested in this little puzzle.

data have;
	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;

proc sort data=work.have;
	by oldid changedate;
run;

%global changes;

/* Declare recursive macro to construct the sequence of ID changes */
%macro getChanges(id);
	%local newid date;
	%let newid=;
	%let date=;
	data _null_;
		set work.have(where=(oldid=&id));
		call symputx('newid', newid);
		call symputx('date',changedate);
	run;
	%if %length(&newid)>0 %then
	%do;
		%let changes=&changes/&newid:&date;
		%getChanges(&newid);
	%end;
%mend;

/* Create a template output data set of Top/Starting/Root Original Ids */
proc sql;
	create table work.want AS
	select 
		. AS asof length=8 /* Place holder */
		,a.oldid AS origid length=4
		,. AS currid length=4 
		,. AS changecount length=3
	from work.have a
	where not exists (select 'x' from work.have b where a.oldid =b.newid);
quit;

data work.want(DROP=rc changes);
	SET work.want;
	call symput('changes','');
	rc = dosubl('%getChanges('||origid||')');
	changes = symget('changes');
	changecount= countc(changes,'/');
	currid = scan(scan(changes,-1,'/'),1,':');
	asof =  INPUT(scan(scan(changes,-1,'/'),2,':'),best.);
	format asof mmddyy10.;
run;

Hope this helps,

Ahmed

[bobsas1]

Thanks, Ahmed.

The output has duplicate rows as shown below and it does not include the origid (1) or the correct changecount (number of times id 4  changed from the original ID), which should be 3 (1->2, 2->3, 3->4).  

asof	origid	currid	changecount
07/10/2010	.	4	2
07/10/2010	.	4	2
07/10/2010	.	4	2

[AhmedAl_Attar]

Hi @bobsas1
Can you please tell us what you expecting to see/get based on the sample data you have included in your original attachment?

It will help clarify lots of potential misunderstanding and misinterpretation.

Thanks,
Ahmed

[bobsas1]

Hi Ahmed, 

My apologies.  I think I included that in the original post, but maybe it didn't display well:

asof	origid	currid	changecount
08/15/2010	1	4	3

-AND-

changedate	oldid	newid
01/01/2010	1	2
07/01/2010	2	3
07/10/2010	3	4

[AhmedAl_Attar]

@bobsas1 

Try this 

data have;
	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;

proc sort data=work.have;
	by oldid changedate;
run;

%global changes;

/* Declare recursive macro to construct the sequence of ID changes */
%macro getChanges(id,outDs);
	%local newid date;
	%let newid=;
	%let date=;
	data child;
		set work.have(where=(oldid=&id));
		call symputx('newid', newid);
		call symputx('date',changedate);
	run;
	%if %length(&newid)>0 %then
	%do;
		PROC APPEND BASE=&outDs DATA=child; RUN;
		%let changes=&changes/&newid:&date;
		%getChanges(&newid,&outDs);
	%end;
%mend;

/* Create a template output data set of Top/Starting/Root Original Ids */
proc sql;
	create table work.want AS
	select 
		'15aug2010'd AS asof length=8 format=mmddyy10.
		,a.oldid AS origid length=4
		,. AS currid length=4 
		,. AS changecount length=3
	from work.have a
	where not exists (select 'x' from work.have b where a.oldid =b.newid);
quit;

data work.want(DROP=rc changes);
	SET work.want;
	/* Reset */
	call symput('changes','');
	dsName=cats('_',origid,'_children');
	rc = dosubl('proc delete data='||dsName||'; run;');

	/* Get Children(s) */
	rc = dosubl('%getChanges('||origid||','||dsName||')');
	changes = symget('changes');
	changecount= countc(changes,'/');
	currid = scan(scan(changes,-1,'/'),1,':');
run;

This version of the code will generate separate data set for every set of children records, and the name of the data set will be stored in a column called: 'dsName'

asof	origid	currid	changecount	dsName
08/15/2010	1	4	3	_1_children
08/15/2010	10	12	2	_10_children
08/15/2010	22	4	1	_22_children

[bobsas1]

Hi Ahmed,

The code you provided is excellent!  How do I modify it so that the recursive macro only evaluates the table "have" for an ID that I specify, such as if I only want the historical path for ID 4? In practice, I will have a set of current ID's I want the history of, so it will be useful if the code references a separate table (instead of just specifying ID "4" in the code) that contains a list of current ID's I want the history.

Sincerely,

R

[AhmedAl_Attar]

@bobsas1 

How about this approach

/* Create sample data of all available IDs and their changes over time */
data all_ids;
	attrib oldid newid length=8 changedate informat=mmddyy10. format=mmddyy10.;
	input oldid newid changedate;
	datalines;
1 2 1/1/10
2 3 7/1/10
3 4 7/10/10
10 11 1/1/10
11 12 8/1/10
;
run;

/* 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;


%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;

/* Usage examples */
%getIdChangeHistory(p_id=4, p_asOfDate='15Aug2010'd, p_idsDsName=work.all_ids, p_outSmryDsName=work._4_smry, p_outChngHistDsName=work._4_changeHistory);
%getIdChangeHistory(p_id=12, p_asOfDate=%sysfunc(date()), p_idsDsName=work.all_ids, p_outSmryDsName=work._12_smry, p_outChngHistDsName=work._12_changeHistory);

Note: You can name your output data sets as you like. The names I used were for illustrations only.

[bobsas1]

/*this code runs with no errors, but does not produce any output tables except "have"*/

%global changes;

%macro getidchangehistory(
p_id=4
,p_asfdate='15Aug2020'd
,p_idsdsname=work.have
,p_outsmrydsname=work.changes
,p_outchnghistsaname=work.chngrec
);

[AhmedAl_Attar]

Have you checked you work library?
The code does not use proc print.

[bobsas1]

Hi Ahmed,

Yes. Only table "Have" exists in the library  "Work".

The p_asofdate I actually used is '15Aug2010'd

Regards,

[AhmedAl_Attar]

Hi,
That's interesting!!
I just copied the entire code I had sent you, changed the first data set name from all_ids to have, and updated the usage examples to:
/* Usage examples */
%getIdChangeHistory(p_id=4, p_asOfDate='15Aug2010'd, p_idsDsName=work.have, p_outSmryDsName=work._4_smry, p_outChngHistDsName=work._4_changeHistory);
%getIdChangeHistory(p_id=12, p_asOfDate=%sysfunc(date()), p_idsDsName=work.have, p_outSmryDsName=work._12_smry, p_outChngHistDsName=work._12_changeHistory);

And everything works as expected!?

[bobsas1]

Hi Ahmed,

Thank you so much.  My apologies, but I misunderstood originally.  It works now (however, table work.child is null).  I hope you can help with this other part- please advise if a new thread is warranted:

I wanted a section of the code that allows me to input many ID's from a separate table and have the macro produce one table (like p_outsmrydsname) with the results combined for all id's.  For example:

1. The new table with many ID's that I want the macro to produce origid, currid, changecount results would like like this:

currid

4

12

...and so on...

2. and the output would look something like:

asof	origid	currid	changecount
08/15/2010	1	4	3
08/15/2010	22	4	1
08/15/2010	10	12	2
and so on....

[andreas_lds]

Can you post data in usable form an show the output you expect, please.

[AhmedAl_Attar]

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