Can you post what the input data looks like in SAS? And what you want the output data to look like?
I think you want for every record, two new variables
Absent your posting testable date ...
Assuming your data are sorted by the time stamp UPDATEDDATE, then this untested code would be a useful approach: It uses hash objects name FREQ (taken from a data set made by proc freq) and PRIOR (built dynamically during the "DATA WANT" step):
proc freq data=have noprint;
tables actorid / out=lookupfreqs (keep=actorid count rename=(count=actor_freq));
run;
data want;
set have;
if _n_=1 then do;
if 0 then set lookupfreqs;
declare hash freq(dataset:'lookupfreqs');
freq.definekey('actorid');
freq.definedata('actor_freq');
freq.definedone();
format prior_update datetime20.;
declare hash prior();
prior.definekey('actorid');
prior.definedata('prior_update');
prior.definedone();
end;
rc=freq.find();
rc=prior.find();
if rc=0 then time_lapse=updateddate - prior_update;
prior_update=updateddate;
rc=prior.replace();
run;
Notes:
One could also re-sort the dataset by actorid/updateddate, and then more easily (i.e. without a hash object) get time_lapse in a data step, but I assume your data would be large enough to make a proc sort costly.
Use proc freq to get counts and then transpose to reshape the data to the form you want.
Can you post the code?
Can you post what the input data looks like in SAS? And what you want the output data to look like?
I think you want for every record, two new variables
Absent your posting testable date ...
Assuming your data are sorted by the time stamp UPDATEDDATE, then this untested code would be a useful approach: It uses hash objects name FREQ (taken from a data set made by proc freq) and PRIOR (built dynamically during the "DATA WANT" step):
proc freq data=have noprint;
tables actorid / out=lookupfreqs (keep=actorid count rename=(count=actor_freq));
run;
data want;
set have;
if _n_=1 then do;
if 0 then set lookupfreqs;
declare hash freq(dataset:'lookupfreqs');
freq.definekey('actorid');
freq.definedata('actor_freq');
freq.definedone();
format prior_update datetime20.;
declare hash prior();
prior.definekey('actorid');
prior.definedata('prior_update');
prior.definedone();
end;
rc=freq.find();
rc=prior.find();
if rc=0 then time_lapse=updateddate - prior_update;
prior_update=updateddate;
rc=prior.replace();
run;
Notes:
One could also re-sort the dataset by actorid/updateddate, and then more easily (i.e. without a hash object) get time_lapse in a data step, but I assume your data would be large enough to make a proc sort costly.
Thanks for your help! Your solution worked perfectly
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
Your data is a picture. You need to post data that community members interested in helping you can use in a program. For example.
data fb;
infile cards dsd missover;
input (list of variable names)(informats);
cards;
value,value,value,...
value,value,value,...
value,value,value,..
;;;;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.