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.
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
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 |
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 |
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 |
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
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.
/*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
);
Hi Ahmed,
Yes. Only table "Have" exists in the library "Work".
The p_asofdate I actually used is '15Aug2010'd
Regards,
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.... |
Can you post data in usable form an show the output you expect, please.
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
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.