Hi, I have data as example following I try to merging with hash. But I can not count exactly. Also I try split as below table as group of ndcID but only get only once value for every group of ndcID. id sum max count linkid ndcID 1 10 278 1 (should 4) 1 AA 2 10 181 2 (should 2) 1 AB 3 20 15 1 (should 2) 2 AA 4 14 28 2 (should 2) 2 AB 5 30 28 3 (should 2) 2 AC options validvarname=any;
data ex2;
length link_id 8;
input link_id study_entry_date :date9.;
format study_entry_date yymmddn8.;
datalines;
1 05JAN2012
2 10FEB2012
run;
data claim;
length link_id 8;
input claim_id ndc_id $ link_id rx_dos_dt :date9. days_supply;
format rx_dos_dt yymmddn8.;
datalines;
1 AA 1 05JAN2014 2
2 AA 1 08JAN2014 3
3 AB 1 14JAN2014 5
4 AD 1 05JAN2013 12
5 AA 2 05JAN2013 10
6 AB 2 10FEB2015 7
7 AC 2 27FEB2015 15
8 AC 3 27JAN2015 15
9 AC 4 27FEB2015 15
10 AA 1 05APR2014 2
11 AA 1 08JAN2015 3
12 AB 1 14JUL2014 5
13 AD 1 25JAN2013 12
14 AA 2 20JAN2013 10
15 AB 2 10MAR2015 7
16 AC 2 27MAR2015 15
17 AC 3 27JUN2015 15
18 AC 4 27AUG2015 15
run;
data ndc;
input ndc_id $;
datalines;
AA
AB
AC
run;
proc sort data=claim;
by link_id ndc_id;
run;
data ndcmerge(keep = link_id ndc_id sum max count);
length sum 8 max 8 count 8;
dcl hash bene_hash(dataset:'ex2', ordered:'a');
bene_hash.definekey('link_id');
bene_hash.definedone();
dcl hash ndc_hash(dataset:'ndc', ordered:'a');
ndc_hash.definekey('ndc_id');
ndc_hash.definedone();
* define dynamic hash object;
declare hash a_groups_data;
declare hash groups (ordered:'a');
groups.defineKey('link_id', 'ndc_id');
groups.defineData('link_id', 'ndc_id', 'a_groups_data');
groups.defineDone();
do count = 0 by 0 until (last.link_id);
do sum = 0 by 0 until (last.ndc_id);
set claim end=last_obs;
by link_id ndc_id;
if bene_hash.find() = 0 and ndc_hash.find() = 0 then do;
prevVal = ifn(first.link_id or first.ndc_id, . , lag(rx_dos_dt));
format prevVal yymmddn8.;
diff = rx_dos_dt - prevVal;
diffPre = ifn(first.link_id or first.ndc_id, . , lag(diff));
if diff > diffPre then
max = diff;
else
max = diffPre;
sum ++ days_supply;
if last.link_id or last.ndc_id then
count = count + 1;
if last.ndc_id then do;
output;
if groups.find() ne 0 then do;
put 'Not Found group of NDC then ADD';
a_groups_data = _new_ hash(ordered:'a');
a_groups_data.defineKey('_n_');
a_groups_data.defineData('link_id', 'ndc_id', 'sum', 'max', 'count');
a_groups_data.defineDone();
groups.add();
end;
else if groups.find() eq 0 then do;;
put 'Found group of NDC';
end;
a_groups_data.add();
end;
end;
end;
end;
dcl hiter groups_iterator ('groups');
do while (groups_iterator.next() = 0);
tablename = cats('group_eq_',ndc_id);
a_groups_data.output (dataset:tablename);
end;
run;
... View more