Assuming I understood
data have;
input id date clinic ;
cards;
1 20020408 12135 1 1 1
1 20020715 12135 2 4 4
1 20030124 15109 1 1 5 /*4+1*/
1 20030308 75423 1 1 6 /*4+1+1*/
1 20030505 75423 2 4 9 /*4+1+4*/
1 20030607 75423 3 9 14 /*4+1+9*/
1 20030815 12135 3 9 19 /*9+1+9*/
2 20020615 25875 1 1 1
2 20020717 25875 2 4 4
2 20020824 55415 1 1 5 /*4+1*/
2 20020907 25875 3 9 10 /*9+1*/
2 20021215 25875 4 16 17 /*16+1*/
2 20030108 25875
;
run;
data want;
if _n_=1 then do;
if 0 then set have;
declare hash h();
declare hiter hi('h');
h.definekey('clinic');
h.definedata('clinic','count','countsquare');
h.definedone();
end;
set have;
by id;
if first.id then h.clear();
if h.find()=0 then do;count=count+1;countsquare=count**2;h.replace();end;
else do;count=1;countsquare=1;h.replace();end;
_clinic=clinic;_countsquare=countsquare;sum=0;
do while(hi.next()=0);
if _clinic ne clinic then sum+countsquare;
end;
sum+_countsquare ; clinic=_clinic;
drop _:;
run;
Hi @km0927 Sorry for the delay as I slept like a baby. Nevertheless, what better help than genius Ksharp who has inspired me a lot. I personally would choose his solution as it is very capable of scaling higher and of course just for the reason I committed last night , I am sharing mine. I hope your weekend is going well.
data have;
input id date clinic ;
cards;
1 20020408 12135 1 1 1
1 20020715 12135 2 4 4
1 20030124 15109 1 1 5 /*4+1*/
1 20030308 75423 1 1 6 /*4+1+1*/
1 20030505 75423 2 4 9 /*4+1+4*/
1 20030607 75423 3 9 14 /*4+1+9*/
1 20030815 12135 3 9 19 /*9+1+9*/
2 20020615 25875 1 1 1
2 20020717 25875 2 4 4
2 20020824 55415 1 1 5 /*4+1*/
2 20020907 25875 3 9 10 /*9+1*/
2 20021215 25875 4 16 17 /*16+1*/
2 20030108 25875
;
run;
proc sql noprint;
select max(c) into : n
from (
select count(id) as c
from have
group by id);
quit;
data want;
array t(&n,2);
array j(*) t:;
array m(%eval(&n*2)) ;
call missing(of t(*),of j(*),of m(*));
do until(last.id);
set have;
by id;
if not missing(clinic) and clinic not in t then do;
n+1;
t(n,1)=clinic;
count=1;
count_square=count**2;
t(n,2)=count;
end;
else if clinic in t then do;
k=whichn(clinic, of t(*));
count=j(K+1)+1;
count_square=count**2;
j(K+1)=count;
end;
m(whichn(clinic,of j(*)))=count_square;
sum=sum(of m(*));
output;
drop m: t: k n ;
end;
run;
thank you for all your help!
thanks to your help, i can complete my work safely.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.