BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ksharp
Super User

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;
novinosrin
Tourmaline | Level 20

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;
km0927
Obsidian | Level 7

thank you for all your help! 

 

thanks to your help, i can complete my work safely.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 17 replies
  • 4438 views
  • 6 likes
  • 5 in conversation