Hi, I have below dataset. I need to count distinct values of id2 by id1 through time. The output I want is the runningcount1 column. Additionally, I need to repeat the same procedure for the last 360 days. Checking last 360 days and counting distinct number of id2 by id1. If it goes less than 360 days back, then it should count distinct values of id2 by id1 however many days it goes back. The output I want is in runningcount2 column. Data is sorted by id1 and time. Thank you very much.
id1 id2 time runningcount1 runningcount2
1 A 20131128 1 1
1 B 20140214 2 2
1 C 20140530 3 3
1 C 20140622 3 3
1 D 20140831 4 4
1 D 20141220 4 3
1 A 20150217 4 3
1 A 20150302 4 3
1 C 20150410 4 3
1 D 20150425 4 3
1 E 20150609 5 4
1 F 20151025 6 5
1 C 20160612 6 2
1 D 20180101 6 1
2 A 19900515 1 1
2 B 19900813 2 2
2 E 19910522 3 2
2 A 19910524 3 3
2 F 19910919 4 3
2 G 19920101 5 4
2 A 19930321 5 1
You can do it with a hash table, e.g.:
data have;
input id1 @6 id2 $ @9 time yymmdd8.;
format time date9.;
cards;
1 A 20131128
1 B 20140214
1 C 20140530
1 C 20140622
1 D 20140831
1 D 20141220
1 A 20150217
1 A 20150302
1 C 20150410
1 D 20150425
1 E 20150609
1 F 20151025
1 C 20160612
1 D 20180101
2 A 19900515
2 B 19900813
2 E 19910522
2 A 19910524
2 F 19910919
2 G 19920101
2 A 19930321
;run;
data want;
set have;
by id1;
if first.id1 then do;
if _N_=1 then do;
declare hash h();
rc=h.definedata('lasttime');
rc=h.definekey('id2');
h.definedone();
declare hiter iter('h');
end;
else
h.clear();
end;
rc=h.find();
lasttime=time;
if rc then
h.add();
else
h.replace();
runningcount1=h.num_items;
rc=iter.first();
starttime=intnx('year',time,-1,'same');
runningcount2=0;
do until(iter.next());
if lasttime>=starttime then
runningcount2=runningcount2+1;
end;
keep id1 id2 time runningcount1 runningcount2;
run;
Please post the contents of the excel-file as data-step using datalines statement, so that we don't have to guess how the data looked like when you imported the file.
You can do it with a hash table, e.g.:
data have;
input id1 @6 id2 $ @9 time yymmdd8.;
format time date9.;
cards;
1 A 20131128
1 B 20140214
1 C 20140530
1 C 20140622
1 D 20140831
1 D 20141220
1 A 20150217
1 A 20150302
1 C 20150410
1 D 20150425
1 E 20150609
1 F 20151025
1 C 20160612
1 D 20180101
2 A 19900515
2 B 19900813
2 E 19910522
2 A 19910524
2 F 19910919
2 G 19920101
2 A 19930321
;run;
data want;
set have;
by id1;
if first.id1 then do;
if _N_=1 then do;
declare hash h();
rc=h.definedata('lasttime');
rc=h.definekey('id2');
h.definedone();
declare hiter iter('h');
end;
else
h.clear();
end;
rc=h.find();
lasttime=time;
if rc then
h.add();
else
h.replace();
runningcount1=h.num_items;
rc=iter.first();
starttime=intnx('year',time,-1,'same');
runningcount2=0;
do until(iter.next());
if lasttime>=starttime then
runningcount2=runningcount2+1;
end;
keep id1 id2 time runningcount1 runningcount2;
run;
Thank you very much s_lassen. This was very helpful.
data have;
input id1 @6 id2 $ @9 time yymmdd8.;
format time date9.;
cards;
1 A 20131128
1 B 20140214
1 C 20140530
1 C 20140622
1 D 20140831
1 D 20141220
1 A 20150217
1 A 20150302
1 C 20150410
1 D 20150425
1 E 20150609
1 F 20151025
1 C 20160612
1 D 20180101
2 A 19900515
2 B 19900813
2 E 19910522
2 A 19910524
2 F 19910919
2 G 19920101
2 A 19930321
;run;
proc sql;
select *,(select count(distinct id2) from have where id1=a.id1 and time le a.time) as count1,
(select count(distinct id2) from have where id1=a.id1 and time between a.time-360 and a.time) as count2
from have as a;
quit;
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.